SQL SELF JOIN: Syntax, Examples & Practice

In the Playground's employees table, each row has a manager_id. That number is the id of another row in the same table, because managers are employees too:

SELECT id, first_name, title, manager_id
FROM employees
WHERE id IN (1, 11, 12)
ORDER BY id;

▶ Run this query in the SQL Playground

Laura and Betty both have manager_id = 1, and employee 1 is William. To show the manager's name instead of a number, the table has to be looked up against itself. Give it two aliases, e for the employee and m for the manager, and join them:

SELECT e.id, e.first_name AS employee, e.title,
       m.first_name AS manager, m.title AS manager_title
FROM employees e
JOIN employees m
  ON m.id = e.manager_id
WHERE e.id BETWEEN 11 AND 16
ORDER BY e.id;

▶ Run this query in the SQL Playground

The database treats e and m as two separate tables that happen to hold the same rows. Every column must be prefixed with one of the aliases, otherwise there is no way to say which copy you mean.

SELECT a.column, b.column
FROM table_name a
JOIN table_name b
  ON a.some_column = b.other_column;

A self join works the same way in MySQL, PostgreSQL, SQL Server, Oracle and SQLite, because it is only a normal join.

People at the top of the hierarchy have no manager: their manager_id is NULL, and NULL matches nothing. An inner self join drops them; a left self join keeps them.

SELECT (SELECT COUNT(*) FROM employees) AS employees,
       COUNT(*) AS inner_join_rows,
       (SELECT COUNT(*)
        FROM employees e
        LEFT JOIN employees m ON m.id = e.manager_id) AS left_join_rows
FROM employees e
JOIN employees m
  ON m.id = e.manager_id;

▶ Run this query in the SQL Playground

Of 120 employees, 110 have a manager. The other ten only appear with a LEFT JOIN, with NULL in the manager columns:

SELECT e.id, e.first_name AS employee, e.title,
       m.first_name AS manager
FROM employees e
LEFT JOIN employees m
  ON m.id = e.manager_id
WHERE e.id BETWEEN 8 AND 13
ORDER BY e.id;

▶ Run this query in the SQL Playground

Grouping a self join by the manager side turns it around: one row per manager, with the people who report to them counted.

SELECT m.id, m.first_name, m.last_name, m.title,
       COUNT(*) AS direct_reports
FROM employees e
JOIN employees m
  ON m.id = e.manager_id
GROUP BY m.id, m.first_name, m.last_name, m.title
ORDER BY direct_reports DESC, m.id;

▶ Run this query in the SQL Playground

10 people manage the other 110. Each extra level of the hierarchy needs one more copy of the table. A third alias reaches the manager's manager:

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

Once two rows of a table sit side by side, their columns can be compared. A classic interview question: which employees earn more than their manager?

SELECT e.first_name || ' ' || e.last_name AS employee, e.salary,
       m.first_name || ' ' || m.last_name AS manager,
       m.salary AS manager_salary
FROM employees e
JOIN employees m
  ON m.id = e.manager_id
WHERE e.salary > m.salary
ORDER BY e.salary - m.salary DESC
LIMIT 5;

▶ Run this query in the SQL Playground

The join pairs each employee with their manager; WHERE then compares a column from each copy. The same pattern finds employees who were hired before their manager:

SELECT e.first_name AS employee, e.hire_date,
       m.first_name AS manager, m.hire_date AS manager_hired
FROM employees e
JOIN employees m
  ON m.id = e.manager_id
WHERE e.hire_date < m.hire_date
ORDER BY e.hire_date
LIMIT 5;

▶ Run this query in the SQL Playground

A join condition does not have to be =

A self join can match rows on any condition. This finds repeat purchases: pairs of orders by the same customer where the second came within 30 days of the first.

SELECT a.user_id,
       a.id AS first_order, a.order_date AS first_date,
       b.id AS next_order,  b.order_date AS next_date
FROM orders a
JOIN orders b
  ON b.user_id = a.user_id
 AND b.order_date > a.order_date
 AND b.order_date <= date(a.order_date, '+30 days')
ORDER BY a.user_id, a.order_date;

▶ Run this query in the SQL Playground

Three conditions describe the pair: same customer, later date, no more than 30 days later. date(…, '+30 days') is SQLite's date arithmetic.

To find rows that share a value, join the table to itself on that value. Several products in the Playground have the same name. Matching on name alone is not enough, though, as the row counts show:

SELECT 'no id condition' AS id_condition, COUNT(*) AS rows_returned
FROM products a JOIN products b ON b.name = a.name
UNION ALL
SELECT 'a.id <> b.id', COUNT(*)
FROM products a JOIN products b ON b.name = a.name AND a.id <> b.id
UNION ALL
SELECT 'a.id < b.id', COUNT(*)
FROM products a JOIN products b ON b.name = a.name AND a.id < b.id;

▶ Run this query in the SQL Playground

So the working query uses <:

SELECT a.id AS id_a, b.id AS id_b,
       a.name, a.price AS price_a, b.price AS price_b
FROM products a
JOIN products b
  ON b.name = a.name
 AND a.id < b.id
ORDER BY a.name, a.id
LIMIT 6;

▶ Run this query in the SQL Playground

A name used by three products gives three pairs. If you only need to know which values are duplicated, not the pairs, GROUP BY name HAVING COUNT(*) > 1 is shorter; see HAVING.

The same < rule builds pairs of people. Colleagues in the same department and the same city:

SELECT a.first_name || ' ' || a.last_name AS employee_a,
       b.first_name || ' ' || b.last_name AS employee_b,
       a.department, a.city
FROM employees a
JOIN employees b
  ON b.department_id = a.department_id
 AND b.city = a.city
 AND a.id < b.id
ORDER BY a.department, a.city, a.id
LIMIT 6;

▶ Run this query in the SQL Playground

Who is the highest paid person in each department? Join each employee to any colleague in the same department who earns more. The top earner is the one for whom no such colleague exists:

SELECT e.department, e.first_name, e.last_name, e.salary
FROM employees e
LEFT JOIN employees h
  ON h.department_id = e.department_id
 AND h.salary > e.salary
WHERE h.id IS NULL
ORDER BY e.department;

▶ Run this query in the SQL Playground

This is the "rows with no match" pattern from the LEFT JOIN lesson, applied to a table and itself. It works in every database, including old versions without window functions.

Self join or window function?

Use a self join when a row points at another row, or when you need pairs. Use a window function when you are comparing a row with its neighbours in some order.

7 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the first ten employees who have a manager, with their name, their title and their manager's full name. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name, e.last_name, e.title,
          m.first_name || ' ' || m.last_name AS manager
   FROM employees e
   JOIN employees m
     ON m.id = e.manager_id
   ORDER BY e.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   e is the employee and m the manager. The inner join leaves out the ten people who have no manager.

2. 2Easy List the employees who have no manager, using a LEFT self join. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.id, e.first_name, e.last_name, e.title
   FROM employees e
   LEFT JOIN employees m
     ON m.id = e.manager_id
   WHERE m.id IS NULL
   ORDER BY e.id;

   ▶ Run this query in the SQL Playground

   The LEFT JOIN keeps everyone; m.id IS NULL keeps those with no matching manager row. 10 employees are at the top.

3. 3Medium For each manager, show their name, the number of direct reports and the average salary of those reports. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT m.first_name, m.last_name,
          COUNT(*) AS direct_reports,
          ROUND(AVG(e.salary)) AS avg_report_salary
   FROM employees e
   JOIN employees m
     ON m.id = e.manager_id
   GROUP BY m.id, m.first_name, m.last_name
   ORDER BY direct_reports DESC, m.id;

   ▶ Run this query in the SQL Playground

   Group by the manager copy and aggregate the employee copy. The salary averaged is e.salary, the reports' pay.

4. 4Medium How many employees earn more than their manager? Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS earn_more_than_manager
   FROM employees e
   JOIN employees m
     ON m.id = e.manager_id
   WHERE e.salary > m.salary;

   ▶ Run this query in the SQL Playground

   Each row of the join is one employee beside their manager, so the two salaries can be compared directly.

5. 5Medium Find employees who work in a different department from their manager. Show the employee, their department, the manager and the manager's department for the first ten. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name AS employee, e.department,
          m.first_name AS manager, m.department AS manager_department
   FROM employees e
   JOIN employees m
     ON m.id = e.manager_id
   WHERE m.department_id <> e.department_id
   ORDER BY e.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   The join condition links the pair; the WHERE compares one column from each copy.

6. 6Challenging Find pairs of customers who share a last name and live in the same city, which may be the same household. Show each pair once. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT a.id AS id_a, b.id AS id_b,
          a.first_name AS first_name_a, b.first_name AS first_name_b,
          a.last_name, a.city
   FROM users a
   JOIN users b
     ON b.last_name = a.last_name
    AND b.city = a.city
    AND a.id < b.id
   ORDER BY a.last_name, a.city, a.id;

   ▶ Run this query in the SQL Playground

   a.id < b.id stops a row matching itself and keeps one row per pair. 7 pairs of customers share a last name and a city.

7. 7Challenging Find the most expensive product in each category with a self join, not MAX or a window function. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT p.category, p.name, p.price
   FROM products p
   LEFT JOIN products h
     ON h.category = p.category
    AND h.price > p.price
   WHERE h.id IS NULL
   ORDER BY p.category;

   ▶ Run this query in the SQL Playground

   A product is the most expensive in its category when no product in the same category has a higher price, so the LEFT JOIN finds no match.