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 |
3 |
3 |
1996-11-27 |
2 |
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 |
1 |
Speedy Express |
(503) 555-9831 |
2 |
United Package |
(503) 555-3199 |
3 |
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
© 2019, Alejandro G. Carlstein Ramos Mejia. All rights reserved.