SQL Join Types: Anti-Semi Join

Tables

Table 1: Suppliers

SupplierID

SupplierName

ContactName

Address

City

PostalCode

Country

Phone

10

Refrescos Americanas LTDA

Carlos Diaz

Av. das Americanas 12.890

São Paulo

5442

Brazil

(11) 555 4640

21

Lyngbysild

Niels Petersen

Lyngbysild Fiskebakken 10

Lyngby

2800

Denmark

43844108

22

Zaanse Snoepfabriek

Dirk Luchte

Verkoop Rijnweg 22

Zaandam

9999 ZZ

Netherlands

(12345) 1212

Table 2: Products

ProductID

ProductName

SupplierID

CategoryID

Unit

Price

24

Guaraná Fantástica

10

1

12 – 355 ml cans

4.5

45

Røgede sild

21

8

1k pkg.

9.5

46

Spegesild

21

8

4 – 450 g glasses

12

47

Zaanse koeken

22

3

10 – 4 oz boxes

9.5

48

Chocolade

22

3

10 pkgs.

12.75

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT SupplierName
FROM Suppliers
WHERE NOT EXISTS (
  SELECT ProductName 
  FROM Products 
  WHERE SupplierId = Suppliers.supplierId 
    AND Price > 13
);

Result

SupplierName

Refrescos Americanas LTDA

Lyngbysild

Zaanse Snoepfabriek

Share
Leave a comment

SQL Join Types: Semi Join

Tables

Table 1: Suppliers

SupplierID

SupplierName

ContactName

Address

City

PostalCode

Country

Phone

6

Mayumi’s

Mayumi Ohno

92 Setsuko Chuo-ku

Osaka

545

Japan

(06) 431-7877

10

Refrescos Americanas LTDA

Carlos Diaz

Av. das Americanas 12.890

São Paulo

5442

Brazil

(11) 555 4640

15

Norske Meierier

Beate Vileid

Hatlevegen 5

Sandvika

1320

Norway

(0)2-953010

Table 2: Products

ProductID

ProductName

SupplierID

CategoryID

Unit

Price

13

Konbu

6

8

2 kg box

6

14

Tofu

6

7

40 – 100 g pkgs.

23.25

24

Guaraná Fantástica

10

1

12 – 355 ml cans

4.5

33

Geitost

15

4

500 g

2.5

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
  SELECT ProductName 
  FROM Products 
  WHERE SupplierId = Suppliers.supplierId 
    AND Price < 7
);

Result

SupplierName

Mayumi’s

Refrescos Americanas LTDA

Norske Meierier

Share
Leave a comment

SQL Join Types: Inner Join

Tables

Table 1: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10308

2

7

1996-09-18

3

10309

37

3

1996-09-19

1

10310

77

8

1996-09-20

2

10365 

1996-11-27

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste).

Table 2: Customers

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

37 

Hungry Owl All-Night Grocers 

Patricia McKenna 

8 Johnstown Road 

Cork 

 

Ireland

77 

The Big Cheese 

Liz Nixon 

89 Jefferson Way Suite 2 

Portland 

97201 

USA 

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Customers.ContactName, Orders.OrderDate
FROM Orders
  INNER JOIN Customers 
    ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.OrderId IN (10308, 10309, 10310, 10365)
ORDER BY Orders.OrderId

Result

OrderID

CustomerName

ContactName

OrderDate

10308 

Ana Trujillo Emparedados y helados 

Ana Trujillo 

1996-09-18 

10309 

Hungry Owl All-Night Grocers 

Patricia McKenna 

1996-09-19 

10310 

The Big Cheese 

Liz Nixon 

1996-09-20 

10365 

Antonio Moreno Taquería 

Antonio Moreno 

1996-11-27 

Share
Leave a comment

SQL Join Types: Right (Outer) Join with Exclusion

Tables

Table 1: Employees

EmployeeID

LastName

FirstName

BirthDate

Photo

Callahan 

Laura 

1/9/1958 

EmpID8.pic 

Dodsworth 

Anne 

7/2/1969 

EmpID9.pic 

10 

West 

Adam 

9/19/1928 

EmpID10.pic 

Table 2: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10255 

68 

7/12/1996 

10262 

65 

7/22/1996 

10263 

20 

7/23/1996 

10268 

33 

7/30/1996 

Note: There is no record with EmployeeID = 10 (West Adam).

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
  RIGHT JOIN Employees
    ON Orders.EmployeeID = Employees.EmployeeID
WHERE Employees.EmployeeID IS NULL
ORDER BY Orders.OrderID;

Result

OrderID

LastName

FirstName

NULL

West

Adam 

Share
Leave a comment

SQL Join Types: Right (Outer) Join

Tables

Table 1: Customers

CustomerID

CustomerName

ContactName

Address

City

PostalCode

Country

1

Alfreds Futterkiste

Maria Anders

Obere Str. 57

Berlin

12209

Germany

2

Ana Trujillo Emparedados y helados

Ana Trujillo

Avda. de la Constitución 2222

México D.F.

05021

Mexico

3

Antonio Moreno Taquería

Antonio Moreno

Mataderos 2312

México D.F.

05023

Mexico

37 

Hungry Owl All-Night Grocers 

Patricia McKenna 

8 Johnstown Road 

Cork 

 

Ireland

77 

The Big Cheese 

Liz Nixon 

89 Jefferson Way Suite 2 

Portland 

97201 

USA 

Table 2: Orders

OrderID

CustomerID

EmployeeID

OrderDate

ShipperID

10308

2

7

1996-09-18

3

10309

37

3

1996-09-19

1

10310

77

8

1996-09-20

2

10365 

1996-11-27 

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste)

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  RIGHT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerName 
  IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
       , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result

OrderID

CustomerName

OrderDate

10308 

Ana Trujillo Emparedados y helados 

9/18/1996 

10365 

Antonio Moreno Taquería 

11/27/1996 

10310 

The Big Cheese 

9/20/1996 

Share
Leave a comment