SQL Join Types: Two (Outer) Full Joins

Tables

Table 1: Orders

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

Table 2: Customers

Table 3: Shippers

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

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

© 2019, Alejandro G. Carlstein Ramos Mejia. All rights reserved.