Lesson 8 of 12

LEFT JOIN and join types

INNER JOIN hides rows without a match. The Research department has no employees, so it disappears in a normal join. LEFT JOIN keeps every row of the left table; where there is no match, the right table's columns come back NULL.

INNER JOINOnly rows matching in both

LEFT JOINAll of A + matches from B

RIGHT JOINAll of B + matches from A

FULL OUTER JOINEvery row from both

A = the table after FROM, B = the table after JOIN.

SQL

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

A classic trick: LEFT JOIN with IS NULL finds “things with nothing”, like products that were never ordered:

SQL

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

Self join: employees has manager_id pointing to another employee in the same table. We join the table to itself under two aliases:

SQL

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

In MySQLMySQL supports LEFT JOIN and RIGHT JOIN but not FULL OUTER JOIN. The workaround is LEFT JOIN ... UNION ... RIGHT JOIN.

LEFT JOIN and join types

Exercises