Left Join
SQL · Joining Multiple Tables
Theory
LEFT JOIN (LEFT OUTER JOIN) keeps every row from the left table, the one written first, and adds matching data from the right table. When a left row has no match, it still appears once, with NULL in all of the right table's columns. Matched rows behave exactly as in an INNER JOIN.
This makes LEFT JOIN the standard tool for finding missing relationships: LEFT JOIN orders and then WHERE o.order_id IS NULL lists customers who never ordered (an anti-join). Be careful where you put filters on the right table. A condition in ON only limits which right rows match, while the same condition in WHERE removes the NULL rows and quietly turns the query back into an INNER JOIN.
Why we use it
Business questions often need every item from a main list, even when related data is missing: all employees with their department if any, all customers with their orders if any. LEFT JOIN keeps the complete main list.
When to use it
Use LEFT JOIN when the left table is your main list and right-side data is optional, or to find unmatched rows with IS NULL. Use INNER JOIN when unmatched rows should be excluded.
How it works
- Take a row from the left table
- Look for right rows that satisfy the ON condition
- Any match? Yes: One combined row per match. No: Left row once, NULLs on the right.
- Repeat for every left row
- Return all left rows
Syntax
SELECT columns
FROM t1
LEFT JOIN t2 ON t1.id = t2.t1_id;Example
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;Key points
- Keeps every row from the left table
- Unmatched rows get NULL in the right table's columns
- LEFT JOIN ... WHERE right.key IS NULL finds unmatched records
- Right-table filters in WHERE turn it into an INNER JOIN; put them in ON
- Table order matters: the first table is the one fully kept