Sweaty Imposter

Keeping Every Row With a LEFT JOIN

SQLJoinsJuniorMidUpdated
List every employee with their department name. New hires who are not in a department yet must still appear, with a NULL department name.

Schema

CREATE TABLE departments (
  id    BIGINT PRIMARY KEY,
  name  TEXT NOT NULL
);

CREATE TABLE employees (
  id             BIGINT PRIMARY KEY,
  name           TEXT NOT NULL,
  department_id  BIGINT REFERENCES departments(id)  -- NULL for new hires not yet placed
);

Drill This: Your Query First

The Correct Query

Option (d)

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id;
A LEFT JOIN keeps every row from the left table (employees) and fills the right side with NULLs when nothing matches, which is exactly what the unplaced new hires need. INNER JOIN (a) drops them. RIGHT JOIN (b) keeps every department instead, so empty departments show up and unplaced employees disappear. (c) starts out right, then the WHERE filter throws away the NULL rows the LEFT JOIN kept, which turns it back into an inner join.

Going Deeper

Conditions on the right-hand table go in the ON clause when you want to keep unmatched left rows. LEFT JOIN departments d ON d.id = e.department_id AND d.active keeps every employee, while the same test in WHERE drops anyone without an active department.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

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

Option (b)

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

Option (c)

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE d.name IS NOT NULL;