Example 1
Find each employee and their manager from the same table
Alias e represents the employee row and alias m represents the manager row. LEFT JOIN is used so that Alice (the top-level manager with no manager of her own) still appears with a NULL manager rather than being excluded. Bob and Carol both report to Alice; Dave reports to Bob.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE employees (id INT, name VARCHAR(50), manager_id INT);
INSERT INTO
employees (id, name, manager_id)
VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Carol', 1),
(4, 'Dave', 2);Validated query Shared across supported engines.
SQL
SELECT
e.name AS employee,
m.name AS manager
FROM
employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY
e.id;Expected result Returned rows for the shared example.
| employee | manager |
|---|---|
| Alice | NULL |
| Bob | Alice |
| Carol | Alice |
| Dave | Bob |
Output is identical across all engines.