SQL Join Types: Two (Outer) Full Joins

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 

Table 3: Shippers

ShipperID

ShipperName

Phone

Speedy Express 

(503) 555-9831 

United Package 

(503) 555-3199 

Federal Shipping 

(503) 555-9931 

Query

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
	FULL JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
	FULL JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)
--WHERE Orders.OrderId IN (10308, 10309, 10310, 10365);

Alternative Query

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName 
FROM Orders
  LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
UNION ALL
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM Customers
  LEFT JOIN Orders ON Orders.CustomerID = Customers.CustomerID
  LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
WHERE Orders.CustomerID IS NULL
UNION ALL
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM Shippers
  LEFT JOIN Orders ON Orders.CustomerID = Shippers.ShipperID
  LEFT JOIN Customers ON Customers.CustomerID = Shippers.ShipperID
WHERE Orders.CustomerID IS NULL AND Customers.CustomerID IS NULL 
--AND Orders.OrderId IN (10308, 10309, 10310, 10365)

Result

OrderID

CustomerName

ShipperName

10309   

Hungry Owl All-Night Grocers

Speedy Express

10365   

Antonio Moreno Taquería

United Package

10310   

The Big Cheese

United Package 

10308   

Ana Trujillo Emparedados y helados

Federal Shipping

NULL

Alfreds Futterkiste

NULL

All Queries

CREATE TABLE Orders(OrderID INT, CustomerID INT, EmployeeID INT, OrderDate DATE, ShipperID INT);
INSERT INTO Orders(OrderID, CustomerID, EmployeeID, OrderDate, ShipperID ) VALUES (10308, 2, 7, '1996-09-18', 3);
INSERT INTO Orders(OrderID, CustomerID, EmployeeID, OrderDate, ShipperID ) VALUES (10309, 37, 3, '1996-09-19', 1);
INSERT INTO Orders(OrderID, CustomerID, EmployeeID, OrderDate, ShipperID ) VALUES (10310, 77, 8, '1996-09-20', 2);
INSERT INTO Orders(OrderID, CustomerID, EmployeeID, OrderDate, ShipperID ) VALUES (10365, 3, 3, '1996-11-27', 2);

CREATE TABLE Customers(CustomerID INT, CustomerName VARCHAR(50));
INSERT INTO Customers(CustomerID, CustomerName) VALUES (1, 'Alfreds Futterkiste');
INSERT INTO Customers(CustomerID, CustomerName) VALUES (2, 'Ana Trujillo Emparedados y helados');
INSERT INTO Customers(CustomerID, CustomerName) VALUES (3, 'Antonio Moreno Taquería');
INSERT INTO Customers(CustomerID, CustomerName) VALUES (37, 'Hungry Owl All-Night Grocers');
INSERT INTO Customers(CustomerID, CustomerName) VALUES (77, 'The Big Cheese');

CREATE TABLE Shippers(ShipperID INT, ShipperName VARCHAR(50));
INSERT INTO Shippers(ShipperID, ShipperName) VALUES (1, 'Speedy Express');
INSERT INTO Shippers(ShipperID, ShipperName) VALUES (2, 'United Package');
INSERT INTO Shippers(ShipperID, ShipperName) VALUES (3, 'Federal Shipping');

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName 
FROM Orders
  LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
UNION ALL
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM Customers
  LEFT JOIN Orders ON Orders.CustomerID = Customers.CustomerID
  LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
WHERE Orders.CustomerID IS NULL
UNION ALL
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM Shippers
  LEFT JOIN Orders ON Orders.CustomerID = Shippers.ShipperID
  LEFT JOIN Customers ON Customers.CustomerID = Shippers.ShipperID
WHERE Orders.CustomerID IS NULL AND Customers.CustomerID IS NULL 
--AND Orders.OrderId IN (10308, 10309, 10310, 10365
Share
Leave a comment

SQL Join Types: Two Left (Outer) Joins

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 

Table 3: Shippers

ShipperID

ShipperName

Phone

Speedy Express 

(503) 555-9831 

United Package 

(503) 555-3199 

Federal Shipping 

(503) 555-9931 

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, Shippers.ShipperName
FROM ((Orders
	LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
	LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)
WHERE Orders.OrderId IN (10308, 10309, 10310, 10365);

Result

OrderID

CustomerName

ShipperName

10308 

Ana Trujillo Emparedados y helados 

Federal Shipping 

10309 

Hungry Owl All-Night Grocers 

Speedy Express 

10310 

The Big Cheese 

United Package 

10365 

Antonio Moreno Taquería 

United Package 

Share
Leave a comment

SQL Join Types: Inner Join and Left (Outer) 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 

Table 3: Shippers

ShipperID

ShipperName

Phone

Speedy Express 

(503) 555-9831 

United Package 

(503) 555-3199 

Federal Shipping 

(503) 555-9931 

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, Shippers.ShipperName
FROM ((Orders
	INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
	LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)
WHERE Orders.OrderId IN (10308, 10309, 10310, 10365);

Result

OrderID

CustomerName

ShipperName

10308 

Ana Trujillo Emparedados y helados 

Federal Shipping 

10309 

Hungry Owl All-Night Grocers 

Speedy Express 

10310 

The Big Cheese 

United Package 

10365 

Antonio Moreno Taquería 

United Package 

Share
Leave a comment

SQL Join Types: Two Inner Joins

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 

Table 3: Shippers

ShipperID

ShipperName

Phone

Speedy Express 

(503) 555-9831 

United Package 

(503) 555-3199 

Federal Shipping 

(503) 555-9931 

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, Shippers.ShipperName
FROM ((Orders
	INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
	INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)
WHERE Orders.OrderId IN (10308, 10309, 10310, 10365);

Result

OrderID

CustomerName

ShipperName

10309 

Hungry Owl All-Night Grocers 

Speedy Express 

10365 

Antonio Moreno Taquería 

United Package 

10310 

The Big Cheese 

United Package 

10308 

Ana Trujillo Emparedados y helados 

Federal Shipping 

Share
Leave a comment

SQL Join Types: Full (Outer) Join with Exclusion

Tables

Table 1: Customers

CustomerId

FirstName

LastName

BookId

1

Joe

Blow

1

2

Jane

Doe

2

3

Harry

Crow

2

4

Jeffrey

Snow

0

Table 2: Books

BookId

OrderTitle

1

Star Wars

2

Jurassic Park

3

Little Women

4

Tom Sawyer

Query

SELECT *
FROM Customers
  FULL OUTER JOIN Books
    ON Customers.BookId = Books.BookId
WHERE Customers.CustomerId IS NULL
  OR Books.BookId IS NULL

Result

id

firstname

lastname

book_id

id1

title

1

Jeffrey

Snow

0

NULL

NULL

NULL

NULL

NULL

NULL

3

Little Women

NULL

NULL

NULL

NULL

4

Tom Sawyer

Share
Leave a comment