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

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

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

*

Click to Insert Smiley

SmileBig SmileGrinLaughFrownBig FrownCryNeutralWinkKissRazzChicCoolAngryReally AngryConfusedQuestionThinkingPainShockYesNoLOLSillyBeautyLashesCuteShyBlushKissedIn LoveDroolGiggleSnickerHeh!SmirkWiltWeepIDKStruggleSide FrownDazedHypnotizedSweatEek!Roll EyesSarcasmDisdainSmugMoney MouthFoot in MouthShut MouthQuietShameBeat UpMeanEvil GrinGrit TeethShoutPissed OffReally PissedMad RazzDrunken RazzSickYawnSleepyDanceClapJumpHandshakeHigh FiveHug LeftHug RightKiss BlowKissingByeGo AwayCall MeOn the PhoneSecretMeetingWavingStopTime OutTalk to the HandLoserLyingDOH!Fingers CrossedWaitingSuspenseTremblePrayWorshipStarvingEatVictoryCurseAlienAngelClownCowboyCyclopsDevilDoctorFemale FighterMale FighterMohawkMusicNerdPartyPirateSkywalkerSnowmanSoldierVampireZombie KillerGhostSkeletonBunnyCatCat 2ChickChickenChicken 2CowCow 2DogDog 2DuckGoatHippoKoalaLionMonkeyMonkey 2MousePandaPigPig 2SheepSheep 2ReindeerSnailTigerTurtleBeerDrinkLiquorCoffeeCakePizzaWatermelonBowlPlateCanFemaleMaleHeartBroken HeartRoseDead RosePeaceYin YangUS FlagMoonStarSunCloudyRainThunderUmbrellaRainbowMusic NoteAirplaneCarIslandAnnouncebrbMailCellPhoneCameraFilmTVClockLampSearchCoinsComputerConsolePresentSoccerCloverPumpkinBombHammerKnifeHandcuffsPillPoopCigarette