Keeping everything on the left: LEFT JOIN

The INNER JOIN from the previous lesson has an important side effect: it drops rows that have no match. If an employee has no department (department_id is NULL), they disappear from the result.

Sometimes that's what you want. Often, though, it isn't: in an HR report it's essential to see all employees, including those without a department, and know who they are. That's what LEFT JOIN is for (in full, LEFT OUTER JOIN).

How it works

SELECT columns
FROM   table_a
LEFT JOIN table_b ON table_a.x = table_b.y;

Read in English: "keep all rows of table_a; for each one try to attach a row from table_b; if there's no match, fill the columns from table_b with NULL".

The key word is LEFT: it means "left table", i.e. the one written after FROM. All of its rows survive, always.

-- All employees, including those without a department:
SELECT e.first_name,
       e.last_name,
       d.name AS dipartimento
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

In our dataset this query returns 10 rows (one employee each). Andrea Costa, who has department_id = NULL, appears with dipartimento = NULL instead of disappearing.

Finding the "no match" rows

A very useful consequence: by filtering with IS NULL on a column of the right-hand table you find exactly the rows with no match:

-- Orphan employees: no department assigned
SELECT e.first_name, e.last_name
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id
WHERE  d.id IS NULL;

This trick is used all the time in the real world: "find me orders with no customer", "products with no reviews", "users who have never logged in".

Try it

Review exercise