LEFT JOIN and RIGHT JOIN

Concept

An outer join keeps rows that have no match, filling every column from the missing side with NULL.

• LEFT JOIN keeps every row from the left table.
• RIGHT JOIN keeps every row from the right table.

LEFT OUTER JOIN and LEFT JOIN are the same thing; OUTER is noise.

Syntax

SELECT columns
FROM   left_table  l
LEFT JOIN right_table r ON r.fk = l.id;

Example

-- Every department, even the ones with nobody in them
SELECT d.name              AS department,
       COUNT(e.id)         AS headcount,
       COALESCE(SUM(e.salary), 0) AS payroll
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY headcount DESC;

Explanation

Research appears with a headcount of 0. Two details make that work:

• COUNT(e.id), not COUNT(*). The unmatched department still produces one row, so COUNT(*) would report 1. COUNT(e.id) counts non NULL employee ids, which is 0.
• COALESCE(SUM(...), 0), because SUM over no rows is NULL, not zero.

The mistake that undoes a LEFT JOIN

-- Broken: this is an INNER JOIN wearing a LEFT JOIN costume
SELECT d.name, e.first_name
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE  e.status = 'active';

-- Correct: put the condition in the ON clause
SELECT d.name, e.first_name
FROM   departments d
LEFT JOIN employees e
       ON e.dept_id = d.id
      AND e.status = 'active';

-- Also correct: allow the NULL side through explicitly
SELECT d.name, e.first_name
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE  e.status = 'active' OR e.id IS NULL;

This is the most important rule about outer joins. For an unmatched department, every e. column is NULL, so e.status = 'active' evaluates to UNKNOWN and WHERE drops the row. The outer join did its job and the WHERE clause undid it.

Rule of thumb: conditions on the preserved table go in WHERE; conditions on the optional table go in ON.

RIGHT JOIN

-- These two return exactly the same rows
SELECT d.name, e.first_name
FROM   departments d
RIGHT JOIN employees e ON e.dept_id = d.id;

SELECT d.name, e.first_name
FROM   employees e
LEFT JOIN departments d ON d.id = e.dept_id;

Any RIGHT JOIN can be written as a LEFT JOIN by swapping the tables. Most teams standardise on LEFT JOIN so the preserved table is always the first one named, which makes long queries far easier to read.

Important rules

• Unmatched rows come back with NULL in every column of the other table.
• A filter on the optional table in WHERE turns the outer join into an inner join.
• Use COUNT(optional_table.key), never COUNT(*), when counting through an outer join.
• Join order matters for outer joins: swapping the tables changes the result.
• Chaining an INNER JOIN after a LEFT JOIN can also eliminate the preserved rows. Keep outer joins outer all the way down the chain.

Common mistakes

• Filtering the optional table in WHERE and wondering why the zero rows vanished.
• COUNT(*) reporting 1 instead of 0 for empty groups.
• Mixing LEFT and RIGHT joins in one query, which is legal and nearly unreadable.
• Forgetting COALESCE and showing blank cells where a report needs zeroes.

Best practices

• Standardise on LEFT JOIN; write the table you want to preserve first.
• Put optional side filters in ON, preserved side filters in WHERE.
• Wrap aggregates over the optional side in COALESCE(..., 0).
• When a report must show every category, start from the dimension table and left join the facts.

Practice

1. List every customer with their order count, including customers who have never ordered.
2. List every department with the count of active employees only, keeping empty departments visible.
3. Explain why moving AND e.status = 'active' from ON to WHERE changes the answer.