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

SQL Join Types: Left (Outer) Join with Exclusion

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
  LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Orders.CustomerID IS NULL
  AND 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

OrderID

 NULL

Alfreds Futterkiste 

 NULL

 NULL

Share
Leave a comment