10 / 10

OUTER JOINs

LEFT/RIGHT/FULL joins, handling unmatched rows.

What you’ll learn

Keep unmatched rows with LEFT/RIGHT/FULL OUTER JOIN.

-- Customers with (optional) latest order date
SELECT c.CustomerID, c.Name, x.LastOrderDate
FROM dbo.Customers AS c
LEFT JOIN (
  SELECT o.CustomerID, MAX(o.OrderDate) AS LastOrderDate
  FROM dbo.Orders o
  GROUP BY o.CustomerID
) AS x
  ON x.CustomerID = c.CustomerID;

-- FULL OUTER JOIN (rare; use when data can be on either side)
-- SELECT ... FROM A FULL OUTER JOIN B ON ...

Notes

When filtering the outer side, put predicates in the ON clause to avoid turning it into an inner join.
Use COALESCE(a.col, b.col) to pick the available value in FULL joins.