LEFT JOIN vs INNER JOIN: The Difference, and a WHERE-Clause Trap to Avoid

INNER JOIN keeps only matched rows; LEFT JOIN keeps every row from the left table whether it matches or not. That sounds simple, but one specific mistake — filtering the right table in WHERE instead of ON — silently turns a LEFT JOIN back into an INNER JOIN. Here's how to spot and avoid it.

The core difference

An INNER JOIN returns only rows that have a match in both tables — if an employee's department_id doesn't match any row in departments, that employee disappears from the result entirely.

A LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the left table regardless of whether it matches. Where there's no match on the right, the right table's columns come back as NULL instead of the row being dropped.

INNER JOIN — only matched rows

Employees who have a department assigned.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- Any employee with a NULL department_id is silently excluded

LEFT JOIN — every left-table row, matched or not

Every employee, with department_name showing NULL if they have none.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- Employees with no department still appear, with department_name = NULL

The classic gotcha: a WHERE clause that undoes the LEFT JOIN

This is the single most common LEFT JOIN mistake. Adding a condition on the right table's column to WHERE silently turns a LEFT JOIN back into an INNER JOIN — because WHERE runs after the join, and NULL fails almost every comparison.

The trap, and the fix

Goal: list every department, showing its IT_PROG employees if it has any — departments with none should still appear.

-- TRAP: filtering the right table in WHERE
SELECT d.department_name, e.job_id
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.job_id = 'IT_PROG';
-- Departments with NO employees at all are silently dropped, because
-- their e.job_id is NULL, and "NULL = 'IT_PROG'" is never TRUE — the
-- LEFT JOIN's whole point (keep unmatched left rows) is undone.

-- FIX: move the condition into the ON clause instead
SELECT d.department_name, e.job_id
FROM departments d
LEFT JOIN employees e
  ON e.department_id = d.department_id AND e.job_id = 'IT_PROG';
-- Now every department still appears; only which employees join is filtered

Using LEFT JOIN to find "no match" rows (anti-join)

This trap has a genuinely useful flip side: LEFT JOIN ... WHERE right_table.key IS NULL is the standard pattern for finding rows in the left table that have no match at all — departments with zero employees, customers with no orders, and so on. It works precisely because the unmatched rows are the ones where the right-side column is NULL.

INNER JOIN vs LEFT JOIN at a glance