Relationships and JOINs
LEFT, RIGHT and FULL OUTER JOIN
Keep unmatched records.
Understand the concept
LEFT JOIN preserves every left row and fills unmatched right-side columns with NULL. RIGHT JOIN reverses that direction. FULL OUTER JOIN preserves unmatched rows from either side. LEFT JOIN is often clearer when you want a consistent reading direction.
A WHERE predicate on the right table after a LEFT JOIN can remove unmatched rows. To show all customers and any paid orders, put the payment condition in ON. Test an inherently non-NULL right-side key when distinguishing a missing match from a NULL stored value.
Use it for customers without orders, unsold products and reconciliation.
Moving the right-side condition to WHERE and effectively turning the left join into an inner join.
One topic. Three levels of depth.
SELECT c.FullName, o.OrderId FROM lab.Customer AS c
LEFT JOIN lab.SalesOrder AS o ON o.CustomerId = c.CustomerId WHERE o.OrderId IS NULL;Finds Selin, who has no orders.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Show every customer’s order count, including zero.
Show the solution and explanation
SELECT c.CustomerId, COUNT(o.OrderId) AS Orders FROM lab.Customer AS c LEFT JOIN lab.SalesOrder AS o ON o.CustomerId = c.CustomerId GROUP BY c.CustomerId;Selin has 0 orders and the result has 6 customer rows.