OUTER JOIN

After this lesson you can

• Keep rows without a match with LEFT, RIGHT and FULL OUTER JOIN
• Find rows that have no match with a LEFT JOIN and IS NULL

Key takeaways

• LEFT JOIN keeps every row of the table named first, RIGHT JOIN every row of the joined table, and FULL OUTER JOIN every row of both.
• A row with no match gets NULL in every column of the other table.
• To find rows with no match, use a LEFT JOIN, then keep only the rows where the right table's key IS NULL.
• When counting with an outer join, use COUNT(o.OrderId), not COUNT(*), so a customer with no orders counts as 0.
• Put the table you want to keep first and write LEFT JOIN; every RIGHT JOIN can be turned round into one.

An inner join drops the rows that have no match. An outer join keeps them, and fills the other table's columns with NULL.

LEFT and RIGHT

In a join, the table named first (after FROM) is the left table, and the joined one is the right table.

LEFT JOIN (also written LEFT OUTER JOIN) keeps every row of the left table, matched or not:

SELECT COUNT(*) AS Rows
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerId = o.CustomerId;

That's the 830 orders, plus one row for each of the two customers with no orders, with NULL in every Orders column.

RIGHT JOIN keeps every row of the right table instead. These two give the same rows:

SELECT COUNT(*) AS Rows
FROM Orders AS o
RIGHT JOIN Customers AS c
    ON o.CustomerId = c.CustomerId;

Most people write LEFT JOIN and put the table they want to keep first. It reads more naturally, and every RIGHT JOIN can be turned round into one.

Finding what's missing

The NULLs are useful. Which customers have never ordered? Keep every customer with a LEFT JOIN, then keep only the rows where no order matched:

SELECT c.CompanyName, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;

This LEFT JOIN, then IS NULL pattern answers every "which ones have none?" question: products never ordered, employees with no orders, categories with no products.

Counting with an outer join

To count orders per customer, including customers with none, use COUNT on a column of the right table. COUNT(*) would count the NULL row as 1; COUNT(o.OrderId) skips it:

SELECT TOP (4) c.CompanyName, COUNT(o.OrderId) AS Orders
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.CustomerId = o.CustomerId
GROUP BY c.CompanyName
ORDER BY Orders, c.CompanyName;

FULL OUTER JOIN

FULL OUTER JOIN keeps unmatched rows from both tables. Which customers and suppliers share a city?

SELECT COALESCE(c.City, s.City) AS City, c.CompanyName AS Customer, s.CompanyName AS Supplier
FROM Customers AS c
FULL OUTER JOIN Suppliers AS s
    ON c.City = s.City
WHERE COALESCE(c.City, s.City) IN ('Berlin', 'Madrid', 'Tokyo')
ORDER BY City, Customer;

Berlin has both; Madrid has customers but no supplier; Tokyo has a supplier but no customer. COALESCE(c.City, s.City) takes whichever city isn't NULL.

Check yourself

2 questions to try on the lesson page.

So what

Outer joins keep the rows an inner join would quietly drop, and the NULLs they leave behind point straight at what's missing: the customer who never ordered, the product nobody bought.

Key terms

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Cheat sheets

• OUTER JOIN