LEFT, RIGHT & FULL Joins

LEFT JOIN

Returns every row from the left table plus matching rows from the right; unmatched right columns come back as NULL.

SELECT s.name_en, e.course_id, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

RIGHT JOIN

The mirror image. Most teams use only LEFT JOIN and swap the table order instead, because mixing directions in one query is easy to misread.

FULL OUTER JOIN

Unlike MySQL, PostgreSQL supports this natively:

SELECT s.name_en, d.name_en AS department
FROM students s
FULL OUTER JOIN departments d ON s.department = d.code;

Every student appears, every department appears, and the unmatched side is NULL. This is the standard tool for reconciling two datasets:

-- what differs between two systems?
SELECT coalesce(a.id, b.id) AS id,
       a.amount AS system_a, b.amount AS system_b
FROM ledger_a a
FULL OUTER JOIN ledger_b b ON a.id = b.id
WHERE a.amount IS DISTINCT FROM b.amount;

Finding unmatched rows

-- students with no enrollment
SELECT s.name_en
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;

-- the same thing with NOT EXISTS, usually planned just as well and clearer
SELECT name_en FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

The classic outer-join mistake

A filter on the right table belongs in ON, not WHERE. In WHERE it turns the outer join back into an inner join, because NULL fails the comparison:

-- WRONG: students with no 'A' grade disappear entirely
SELECT s.name_en, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.grade = 'A';

-- RIGHT: all students kept
SELECT s.name_en, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id AND e.grade = 'A';

Counting with LEFT JOIN

SELECT s.name_en, count(e.id) AS courses_taken
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name_en;

Use count(e.id), not count(*) — count(*) counts the NULL-filled row and reports 1 for students with no enrollments.

Aggregating in a subquery instead

When you join several one-to-many tables at once, the row counts multiply and your sums are silently wrong. Aggregate each side first:

SELECT s.name_en, ec.courses, pc.payments
FROM students s
LEFT JOIN (SELECT student_id, count(*) AS courses FROM enrollments GROUP BY 1) ec
       ON ec.student_id = s.id
LEFT JOIN (SELECT student_id, sum(amount) AS payments FROM payments GROUP BY 1) pc
       ON pc.student_id = s.id;

This "fan-out" bug is one of the most common causes of inflated numbers in reports.