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

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

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

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

SQL Join Types: Left (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 

11/27/1996 

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 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

 NULL

Alfreds Futterkiste 

 NULL

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