LEFT JOIN

Definition

LEFT JOIN (or LEFT OUTER JOIN) keeps every row from the LEFT table, matching in data from the right table where possible, and filling NULL where there's no match.

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT e.id, e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Now Vikram DOES appear, with department = NULL — his row from employees (the "left" table) is preserved regardless of whether a match exists. This is the standard way to answer "show me everything from A, plus whatever matches from B" — and, combined with WHERE d.id IS NULL, the anti-join pattern (16.12) for finding exactly the unmatched rows.

Edge Cases and Pitfalls

• The table order matters: employees LEFT JOIN departments preserves employees; swapping to departments LEFT JOIN employees would instead preserve departments (and would show Marketing with NULL employee columns) — this is a completely different query despite looking superficially similar.
• Filtering on the RIGHT table's column in WHERE (instead of ON) can silently undo the LEFT JOIN's purpose (already covered in 7.11) — a genuinely common real mistake worth repeating here in the SQL-syntax context specifically.
• LEFT JOIN combined with an aggregate (COUNT, SUM) needs care: COUNT(d.id) counts only matched rows (skipping the NULLs from unmatched left rows), while COUNT(*) counts every left row regardless — choosing the wrong one silently changes what's being measured.

Key Takeaways

• LEFT JOIN preserves every row from the left table, filling NULL for unmatched right-side columns.
• Table order determines which side is preserved — swapping tables changes the query's meaning entirely.
• Combined with WHERE right.col IS NULL, LEFT JOIN becomes the anti-join pattern for finding unmatched rows.