Day 10 — LEFT JOIN and self joins
LEFT JOIN is how you find what is missing — inactive customers, unsold products, students who never attended.
LEFT JOIN keeps everything on the left
Every row from the first table stays, whether or not it matches. Where there is no match, the right-hand columns come back NULL.
16 rows this time, not 15. Bose Logistics appears with NULLs — it is a customer with no orders.
The killer use: finding the gaps
LEFT JOIN plus IS NULL gives you "everything with no matching record". Learn this pattern; you will use it constantly.
Same idea, products that have never been ordered:
Counting with a LEFT JOIN
COUNT(o.OrderID), not COUNT(*). COUNT(*) counts the placeholder row too, so a customer with no orders would show 1 instead of 0.Self join — a table joined to itself
Employees stores a ManagerID that points at another row in the same table. Join the table to itself, with two different aliases, to read both.
LEFT JOIN matters here too — Rajesh Nair has no manager, and an INNER JOIN would silently drop the head of the company.
Try these yourself
- List all products with the quantity ordered, including ones never ordered.
- Find customers who have never placed an order.
- Count orders per customer, showing zeros.
- List every employee with their manager name.
- Find employees who manage nobody.
Want this taught properly?
This tutorial covers the basics. Our SQL course in Noida takes you through window functions, CTEs and stored procedures on a real database, with projects and mentor support.
See the SQL course →