LEFT, RIGHT and FULL OUTER JOIN

An inner join hides customers who never ordered. Often those are exactly the rows you want: products that never sold, categories with nothing in them. An outer join keeps every row of one table (or both) and fills the columns of the missing partner with NULL.

1SELECT c.FirstName, o.OrderId, o.OrderDate2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders AS o4    ON o.CustomerId = c.CustomerId5ORDER BY c.CustomerId, o.OrderId;

FirstName  OrderId  OrderDate
---------  -------  ----------
Asha             1  2024-02-03
Asha             3  2024-03-01
Ben              2  2024-02-10
Ben              9  2024-06-01
Chloe            6  2024-04-20
Dev              4  2024-03-15
Dev              7  2024-05-05
Ella          NULL  NULL
Farid            5  2024-04-02
Farid           10  2024-06-12
Grace         NULL  NULL
Zoë              8  2024-05-18

LEFT JOIN (or LEFT OUTER JOIN) keeps every row of the table on its left, here Customers. Ella and Grace now appear once each, with NULL in the Orders columns.

Finding rows with no partner

The NULLs are useful: keep only the rows where the right side is missing, and you have the customers who never ordered. Test a column that cannot be NULL in a real match, such as the right table’s primary key:

1SELECT c.CustomerId, c.FirstName, c.JoinedOn2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId4WHERE o.OrderId IS NULL;

CustomerId  FirstName  JoinedOn
----------  ---------  ----------
         5  Ella       2023-09-14
         7  Grace      2024-01-08

All four join types

1SELECT c.CategoryId, c.Name AS Category,2       p.ProductId, p.Name AS Product, p.Stock3FROM dbo.Categories AS c4LEFT JOIN dbo.Products AS p5    ON p.CategoryId = c.CategoryId;

Plumbing is a category with no products yet. A LEFT JOIN from Categories shows it, and COALESCE replaces the NULL with something readable:

1SELECT cat.Name AS Category, COALESCE(p.Name, N'(no products)') AS Product2FROM dbo.Categories AS cat3LEFT JOIN dbo.Products AS p ON p.CategoryId = cat.CategoryId4WHERE cat.CategoryId >= 55ORDER BY cat.CategoryId, p.Name;

Category  Product
--------  ------------------
Paint     Masonry paint 10 L
Paint     Paint roller set
Safety    Safety goggles
Plumbing  (no products)

RIGHT JOIN is the same as LEFT JOIN with the two tables swapped, so Products RIGHT JOIN Categories gives the result above. Most people only use LEFT JOIN and write the table whose rows must all be kept first; a query mixing LEFT and RIGHT is hard to read.

FULL JOIN keeps unmatched rows from both sides. Products.CategoryId allows NULL, so add a gift voucher that belongs to no category, then list everything that has no partner:

1INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)2VALUES (N'Gift voucher', NULL, 20.00, 100, N'each', '2024-06-01');3 4SELECT cat.Name AS Category, p.Name AS Product5FROM dbo.Categories AS cat6FULL JOIN dbo.Products AS p ON p.CategoryId = cat.CategoryId7WHERE cat.CategoryId IS NULL OR p.ProductId IS NULL8ORDER BY Category;

Category  Product
--------  ------------
NULL      Gift voucher
Plumbing  NULL

Mistake 1: filtering the outer table in WHERE

Show every customer with their pending order, if they have one. Putting the status test in WHERE looks right, but returns only two customers:

1SELECT c.FirstName, o.OrderId, o.Status2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId4WHERE o.Status = 'Pending';

FirstName  OrderId  Status
---------  -------  -------
Zoë              8  Pending
Ben              9  Pending

WHERE runs after the join. For customers without a pending order, o.Status is NULL or another status, the test is not true, and the row is thrown away, the very rows the LEFT JOIN kept. Move the test into ON: then it only decides which orders match, and every customer survives:

1SELECT c.FirstName, o.OrderId, o.Status2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders AS o4    ON o.CustomerId = c.CustomerId5   AND o.Status = 'Pending'6ORDER BY c.CustomerId;

FirstName  OrderId  Status
---------  -------  -------
Asha          NULL  NULL
Ben              9  Pending
Chloe         NULL  NULL
Dev           NULL  NULL
Ella          NULL  NULL
Farid         NULL  NULL
Grace         NULL  NULL
Zoë              8  Pending

Mistake 2: an inner join after an outer join

Ella and Grace survive the LEFT JOIN to Orders with a NULL OrderId. The next inner join needs oi.OrderId = o.OrderId, NULL matches nothing, and they are gone again:

1SELECT c.FirstName, o.OrderId, oi.ProductId2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders AS o ON o.CustomerId = c.CustomerId4JOIN dbo.OrderItems AS oi ON oi.OrderId = o.OrderId5WHERE c.CustomerId IN (5, 6, 7)6ORDER BY c.CustomerId, o.OrderId, oi.ProductId;

FirstName  OrderId  ProductId
---------  -------  ---------
Farid            5         10
Farid            5         11
Farid           10          3
Farid           10          6

Once a table has been outer joined, every join hanging off it must be an outer join too:

1SELECT c.FirstName, o.OrderId, oi.ProductId2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders     AS o  ON o.CustomerId = c.CustomerId4LEFT JOIN dbo.OrderItems AS oi ON oi.OrderId   = o.OrderId5WHERE c.CustomerId IN (5, 6, 7)6ORDER BY c.CustomerId, o.OrderId, oi.ProductId;

FirstName  OrderId  ProductId
---------  -------  ---------
Ella          NULL       NULL
Farid            5         10
Farid            5         11
Farid           10          3
Farid           10          6
Grace         NULL       NULL

1SELECT c.FirstName, o.OrderId, o.OrderDate2FROM dbo.Customers AS c3LEFT JOIN dbo.Orders AS o4    ON o.CustomerId = c.CustomerId5   AND o.Status = 'Shipped'6ORDER BY c.CustomerId, o.OrderId;

FirstName  OrderId  OrderDate
---------  -------  ----------
Asha             1  2024-02-03
Asha             3  2024-03-01
Ben              2  2024-02-10
Chloe         NULL  NULL
Dev              4  2024-03-15
Dev              7  2024-05-05
Ella          NULL  NULL
Farid            5  2024-04-02
Farid           10  2024-06-12
Grace         NULL  NULL
Zoë           NULL  NULL