LEFT and RIGHT OUTER JOIN

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Preserve one side of a relationship while counting optional matches.

Run the example

SELECT TOP (5) c.CustomerID, COUNT(o.OrderID) AS OrderCount
FROM dbo.Customers AS c
LEFT JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID
ORDER BY c.CustomerID;

Read it step by step

1. LEFT JOIN preserves every customer even if no order matches.
2. COUNT(o.OrderID) counts matched orders; it would be zero for a customer with none.
3. Grouping restores one row per customer, and ORDER BY fixes the TOP (5) sample.

Check the result

Every customer has orders in the unmodified dataset; the outer join still expresses the intended preserve-customers rule.

Practice

Use RIGHT JOIN to preserve every category while counting inactive products in it.

SELECT c.CategoryID, COUNT(p.ProductID) AS InactiveProducts
FROM dbo.Products AS p
RIGHT JOIN dbo.Categories AS c
  ON p.CategoryID = c.CategoryID AND p.IsActive = 0
GROUP BY c.CategoryID
ORDER BY c.CategoryID;