$ ./guess_the_languageSQL & Databases
SQL & Databases · hard · #12

Why is the CEO missing from the result?

SELECT e.name, m.name AS boss
FROM emp e
JOIN emp m ON e.manager_id = m.id;
-- the CEO (manager_id NULL) is missing
  1. A table can never be joined to itself
  2. m.name overwrites e.name here
  3. Aliases hide rows from the output
  4. An inner join drops the NULL manager
NULL = m.id is never true, so the inner self-join discards the CEO; a LEFT JOIN keeps it with a NULL boss.
Play SQL & Databases ▸