LEFT, RIGHT, FULL JOIN

Learning Focus

Use this lesson to understand outer joins and when to use LEFT, RIGHT, and FULL OUTER joins.

LEFT JOIN

Returns all rows from the left table, plus matching rows from the right. Non-matching rows get NULL for right columns.

-- All employees, even those without a department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Use when: You want all records from the primary table regardless of a match.

RIGHT JOIN

Returns all rows from the right table, plus matching rows from the left.

-- All departments, even those with no employees
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

FULL OUTER JOIN

Returns all rows from both tables. NULLs fill where there's no match.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Finding Non-Matching Rows (Anti-Join)

-- Employees with NO department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- Departments with NO employees
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.id IS NULL;

Common Pitfalls

Pitfall	Consequence	Prevention
Confusing LEFT and RIGHT	Wrong base table	Prefer LEFT JOIN always — flip table order instead
FULL OUTER without COALESCE	Many NULLs in output	Use COALESCE(e.id, d.dept_id) for ID columns
Anti-join missing IS NULL	Returns all rows	Filter with WHERE right_table.id IS NULL

Quick Reference

-- LEFT JOIN: keep all from left
FROM a LEFT JOIN b ON a.id = b.a_id

-- RIGHT JOIN: keep all from right
FROM a RIGHT JOIN b ON a.id = b.a_id

-- FULL OUTER JOIN: keep all from both
FROM a FULL OUTER JOIN b ON a.id = b.a_id

-- Anti-join: find non-matching rows
FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.id IS NULL

What's Next

• Next: CROSS JOIN - Generate all combinations of rows.
• Module Overview - Return to this module index.