LEFT JOIN in SQL Server: keep everyone on the left
LEFT JOIN keeps every row from the left table. If the right table has no match, those columns come back NULL. That is the join you use for “everyone, and the extra if it exists.”
Every customer, last order if they have one
SELECT c.customer_id, c.first_name, c.last_name,
o.order_id, o.order_date, o.total_amount
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.customer_id
ORDER BY c.last_name, o.order_date;Amy appears once per order. If she never ordered, she still appears, with NULL order columns. To find only those gaps, see LEFT JOIN … IS NULL.
WHERE o.status = 'Shipped' after a LEFT JOIN quietly deletes the unmatched left rows, because NULL is not Shipped. Put filters on Orders in the ON clause instead.
At work
This is the join you will write most after INNER. “All products, sales if any.” “All staff, orders this month if any.”
Try it on Harbour & Co
Becky Ashworth (Support) needs this now: “Who signed up but never ordered? I want to send them a nudge.” Write the query, run it, send the results. On the paid plan.
Take the ticket →Learn it on a real ticket
Monday is free on WorkSQL. A colleague’s ticket lands, you write the T-SQL, we mark it. No videos. Five minutes.
Clock in free See Monday’s tickets