Level 5 · Who reports to whom

A self join joins a table to itself. It is how you work with hierarchies stored in one table: employees and managers, categories and parent categories, referrals between customers.

In employees, manager_id holds the employee_id of each person’s manager. To show the manager’s name, you need a second copy of the table playing the role of “manager”:

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

The aliases are not optional here. e and m are two roles for the same table, and every column must say which role it comes from.

Why LEFT JOIN

The CEO has manager_id NULL. With an inner join, NULL = m.employee_id never matches, and the CEO disappears from the org chart. LEFT JOIN keeps her, with NULL manager columns. In any hierarchy, the top of the tree has no parent, so self joins are almost always LEFT joins.

Concatenating text

|| joins strings in Postgres, DuckDB, SQLite, Oracle and Snowflake:

e.first_name || ' ' || e.last_name AS employee_name

MySQL uses CONCAT(first_name, ' ', last_name) (in MySQL, || means OR unless a SQL mode is set). BigQuery supports both || and CONCAT. CONCAT also exists in Postgres and DuckDB, so it is the most portable choice.

They differ on NULLs: 'Hale' || NULL is NULL, while CONCAT('Hale', NULL) returns 'Hale' in Postgres and DuckDB (and NULL in MySQL). For the CEO, m.first_name || ' ' || m.last_name is NULL, which is what Nicole asked for. CONCAT would return a single space, which looks blank but is not NULL and would break a later IS NULL check.

Going further up the tree

One self join gives one level. “Manager’s manager” needs a second join to a third alias:

LEFT JOIN employees AS m  ON m.employee_id  = e.manager_id
LEFT JOIN employees AS mm ON mm.employee_id = m.manager_id

For a hierarchy of unknown depth, Postgres, DuckDB, MySQL 8 and BigQuery support recursive CTEs (WITH RECURSIVE), which walk the tree until it ends. That is an advanced interview topic and worth knowing exists.

Common uses for analysts

• Headcount and salary by manager (join, then group by the manager’s name).
• Finding employees who earn more than their manager, a well-known interview question: WHERE e.salary > m.salary.
• Comparing rows within a table, such as orders by the same customer, although window functions (chapter 8) usually do that more simply.