SQL OUTER JOIN

SQL OUTER JOIN Explained

SQL OUTER JOIN combines records from 2 tables.

It starts with all records from the first table.

The first table is then combined with a second table.

Possible JOIN styles are LEFT, RIGHT, or FULL.

SQL OUTER JOINS: LEFT, RIGHT, and FULL

Example

#

Problem: List all suppliers and their products, including suppliers with no products.

SELECT CompanyName, ProductName
  FROM Supplier 
  LEFT OUTER JOIN Product ON Supplier.Id = Product.SupplierId

Syntax

LEFT OUTER JOIN syntax.

SELECT column-names
  FROM table-name1 
  LEFT OUTER JOIN table-name2 ON column-name1 = column-name2
 WHERE condition

RIGHT OUTER JOIN syntax.

SELECT column-names
  FROM table-name1 
 RIGHT OUTER JOIN table-name2 ON column-name1 = column-name2
 WHERE condition

FULL OUTER JOIN syntax.

SELECT column-names
  FROM table-name1 
  FULL OUTER JOIN table-name2 ON column-name1 = column-name2
 WHERE condition

The OUTER keyword is the default and is optional.


More Examples

SQL LEFT OUTER JOIN

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers and the total amount they spent irrespective whether they placed any orders or not.
SELECT OrderNumber, TotalAmount, FirstName, LastName, City, Country
  FROM Customer C 
  LEFT OUTER JOIN [Order] O ON O.CustomerId = C.Id
 ORDER BY TotalAmount

The ORDER BY TotalAmount shows the customers without orders first (i.e. TotalAmount is NULL).

Result:  832 records
OrderNumber TotalAmount FirstName LastName City Country
NULL NULL Diego Roel Madrid Spain
NULL NULL Marie Bertrand Paris France
542912 12.50 Patricio Simpson Buenos Aires Argentina
542937 18.40 Paolo Accorti Torino Italy
542897 28.00 Pascale Cartrain Charleroi Belgium
542716 28.00 Maurizio Moroni Reggio Emilia Italy
543028 30.00 Yvonne Moncada Buenos Aires Argentina
543013 36.00 Fran Wilson Portland USA

SQL RIGHT OUTER JOIN

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List customers that have not placed orders.
SELECT FirstName, LastName, City, Country, TotalAmount
  FROM [Order] O 
 RIGHT OUTER JOIN Customer C ON O.CustomerId = C.Id
 WHERE TotalAmount IS NULL

This returns customers that, when joined, have no matching order.

Result:  2 records
FirstName LastName City Country TotalAmount
Diego Roel Madrid Spain NULL
Marie Bertrand Paris France NULL

SQL FULL OUTER JOIN

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: Match all customers and suppliers by country.
SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry, 
       S.Country AS SupplierCountry, S.CompanyName
  FROM Customer C 
  FULL OUTER JOIN Supplier S ON C.Country = S.Country
 ORDER BY C.Country, S.Country

This returns all possible combinations of suppliers and customers. This includes suppliers without customers in their country, and customers without suppliers in their country, as well as customers and suppliers that are from the same country.

Result: 195 records
FirstName LastName CustomerCountry SupplierCountry CompanyName
NULL NULL NULL Australia Pavlova, Ltd.
NULL NULL NULL Australia G'day, Mate
NULL NULL NULL Japan Tokyo Traders
NULL NULL NULL Japan Mayumi's
NULL NULL NULL Netherlands Zaanse Snoepfabriek
NULL NULL NULL Singapore Leka Trading
Patricio Simpson Argentina NULL NULL
Yvonne Moncada Argentina NULL NULL
Sergio Gutiérrez Argentina NULL NULL
Georg Pipps Austria NULL NULL
Roland Mendel Austria NULL NULL
Pascale Cartrain Belgium NULL NULL
Catherine Dewey Belgium NULL NULL
Bernardo Batista Brazil Brazil Refrescos Americanas LTDA
Lúcia Carvalho Brazil Brazil Refrescos Americanas LTDA
Janete Limeira Brazil Brazil Refrescos Americanas LTDA
Aria Cruz Brazil Brazil Refrescos Americanas LTDA
André Fonseca Brazil Brazil Refrescos Americanas LTDA
Mario Pontes Brazil Brazil Refrescos Americanas LTDA
Pedro Afonso Brazil Brazil Refrescos Americanas LTDA
Paula Parente Brazil Brazil Refrescos Americanas LTDA
Anabela Domingues Brazil Brazil Refrescos Americanas LTDA
Elizabeth Lincoln Canada Canada Ma Maison
Elizabeth Lincoln Canada Canada Forêts d'érables
Yoshi Tannamuri Canada Canada Ma Maison
Yoshi Tannamuri Canada Canada Forêts d'érables
Jean Fresnière Canada Canada Ma Maison

You may also like



Guides