Skip to content
Tutorials Center
My learning

Topics

Lesson 38 of 125 ยท Joins

Self Joins

Learn SQL self joins by joining a table to itself. List employees with their managers, count direct reports, walk up the hierarchy and pair rows without duplicates.

A self join is a join where both sides are the same table. There is no special SELF JOIN keyword: you write an ordinary INNER JOIN or LEFT JOIN and list the same table twice, giving each copy its own alias.

Why would you ever do that? Because a single table often holds rows that relate to other rows of the same table. An employee's manager is also an employee. A product can be compared with other products in its category. A flight connects to another flight. Whenever a question says "compare this row with another row of the same kind", a self join is the tool.

This lesson uses the company database. Its employees table has a manager_id column that holds the id of another employee. The CEO, Kofi Okafor, has manager_id = NULL because nobody manages him.

One table, two roles

Here are a few rows of employees with only the columns that matter:

SQL The manager_id column ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Yusuf Novak and Ivo Rossi have manager_id = 2, so their manager is employee 2, Yara Demir. Yara's manager_id is 1, the CEO. To print each employee next to their manager's name, the query needs to read employees twice: once as "the employee" and once as "the manager". Aliases make this possible. Here e is the employee and m is the manager:

SQL Employees and their managers ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Read the ON clause aloud: "a manager row matches when its id equals the employee's manager_id." The database treats e and m as two separate tables that happen to contain the same rows. Each employee row looks for the one row in m whose id is its manager_id.

Aliases are required. Without them, employees.first_name would be ambiguous: which copy do you mean? A self join without distinct aliases fails with an error like "ambiguous column name". Pick aliases that describe the role (e and m, or emp and boss), not a and b, so the ON clause reads correctly.

Keeping the CEO with LEFT JOIN

The query above returns 35 rows, but the company has 36 employees. The CEO is missing because his manager_id is NULL, which never equals any id, so the inner join drops him. To keep everyone, switch to a LEFT JOIN and label the gap with COALESCE:

SQL Nobody left behind ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The WHERE clause keeps the result short: the CEO plus everyone who reports directly to him. The CEO's row has NULL in every m column, and because NULL || ' ' || NULL is still NULL, COALESCE replaces it with (no manager).

Comparing a row with its manager

Once both rows sit side by side, you can compare any of their columns. Who was hired before their own manager joined the company?

SQL Hired before the boss ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Only Felix Demir: he joined Research in February 2018, and Carlos Tanaka became its head in November. Dates are stored as YYYY-MM-DD text, which sorts in date order, so < works.

The classic interview question is "find employees who earn more than their manager". In this company the answer is nobody, so the next example asks it, gives one person a raise, and asks again. The UPDATE only changes this run's copy:

SQL Earning more than the manager ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first query returns zero rows. After Ivo Andersson's raise to 150,000, the same query finds him, because his manager Omar Tanaka earns 146,000. An empty result is a valid answer, not an error.

You can compute with both rows, too. This one shows how close each person's pay is to their manager's, as a percentage:

SQL Salary as a share of the manager's ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Ivo Andersson is at the top with 80.8%. The 100.0 makes the division use real numbers; with 100 SQLite would divide integers and drop the decimals (see Calculated Columns).

Counting direct reports

Flip the roles and you can ask questions about managers. Start from the manager side and join each manager to the employees who report to them, then group:

SQL Team sizes ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Seven people manage someone. The CEO has seven direct reports: the six department heads and the contractor. Notice that the ON condition is the same relationship as before (e.manager_id = m.id); only the table that drives the question changed.

To list every employee with their report count, including the zeros, use FROM employees AS m LEFT JOIN employees AS e and keep COUNT(e.id). Counting a column of the optional side counts only real matches, so people without reports get 0. Try it in the editor above.

Walking up more than one level

Each extra level of the hierarchy is one more copy of the table. To show an employee, their manager and their manager's manager, join employees three times:

SQL Two levels up ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The Research scientists show Carlos Tanaka as manager and the CEO above him. Carlos himself reports to the CEO, who has no manager, so his managers_manager is NULL; the LEFT JOIN on the last step keeps his row.

This works when you know how many levels you need. For a whole org chart of unknown depth, a fixed number of self joins isn't enough. That's the job of recursive CTEs, which repeat the self join until they reach the top.

Spotting data problems

Self joins are a great data-quality tool. A sensible rule for this company is that people are managed by someone in their own department. Which employees break it?

SQL Managed from another department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Four engineers (department 1) report to the heads of Design or Sales. Maybe it's a mistake, maybe it's a matrix organization; either way the query surfaced it. Also notice who is not listed: the department heads report to the CEO, whose department_id is NULL, and 1 <> NULL is unknown rather than true, so those rows are filtered out. That happens to be what we want here, but keep NULL logic in mind whenever a self join compares nullable columns.

Pairing rows without duplicates

Not every self join follows a parent link. Sometimes you want pairs of rows that share something: two employees with the same last name, two customers in the same city, two products at the same price. The naive version matches every row with itself and lists every pair twice. Compare three versions:

SQL Pairs: three attempts ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first count is 80: 36 rows that match themselves plus 44 real pairings. Adding b.id <> a.id removes the self-matches and leaves 44, but that's 22 pairs counted twice. Requiring b.id > a.id keeps exactly one ordering of each pair. For the four Tanakas the final query lists six pairs, because four people make 4 ร— 3 / 2 = 6 pairs.

Use < or > on a unique column to list each unordered pair once. Use <> only when order matters, for example "who could swap shifts with whom" where (A, B) and (B, A) are different requests.

The same pattern works in any table. Here it finds customers of the shop database who live in the same city and signed up within a month of each other:

SQL Neighbors who joined together ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Only one pair qualifies: Lena Andersson and Felix Garcia, who both live in Bogota and signed up a week apart. Change 31 to 90 and more pairs appear. julianday() turns a date into a day number, so subtracting two of them gives the gap in days (Date and Time Functions covers it in detail).

Performance and dialects

A self join is an ordinary join, so the usual advice applies: the column you join on should be indexed. Primary keys like id always are, but foreign-key style columns like manager_id are not indexed automatically in SQLite, PostgreSQL or SQL Server (MySQL's InnoDB does create an index for a declared foreign key). On a large table, CREATE INDEX idx_employees_manager ON employees (manager_id); makes "find this manager's reports" fast. See Indexes.

Pair queries deserve a second look on big tables: matching on a common value like country can produce millions of pairs. Count first (SELECT COUNT(*) ...) before you select the rows.

Self joins themselves work identically in every database. For deep hierarchies, every major database supports recursive CTEs, and Oracle additionally has its own CONNECT BY syntax, which you'll meet in older Oracle code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does FROM employees AS e JOIN employees AS m ON m.id = e.manager_id leave out the CEO?

    NULL never equals anything, so the CEO's row finds no manager. Use LEFT JOIN to keep it.
  2. 2. A table has 4 employees named Tanaka. How many rows does a self join on last_name with b.id > a.id return for them?

    Each unordered pair appears once: 4 ร— 3 / 2 = 6. With <> you'd get 12, and with no id condition 16.
  3. 3. Which query counts each manager's direct reports?

    Each report row e matches the manager row m whose id is e.manager_id; grouping by the manager counts them.
  4. 4. What does this self join find? ... FROM employees AS e JOIN employees AS m ON m.id = e.manager_id WHERE e.hire_date < m.hire_date

    e is the employee and m their manager, so the condition keeps employees whose hire date is earlier than their manager's.
  5. 5. What happens if you self join without giving the two copies different aliases?

    Each copy needs its own name so every column reference says which copy it means.

Summary

  • A self join lists the same table twice with different aliases, one per role.
  • Hierarchies stored with a parent column (manager_id) are joined with ON m.id = e.manager_id.
  • Use a LEFT JOIN to keep rows without a parent, such as the CEO.
  • Once rows sit side by side you can compare any of their columns: dates, salaries, departments.
  • Each extra hierarchy level is one more copy of the table; unknown depth needs a recursive CTE.
  • To list pairs once, join on the shared value and add b.id > a.id; <> lists each pair in both orders.
  • Index the column you join on, and count pair queries before selecting them.

Next: CROSS JOIN, where you'll combine every row of one table with every row of another on purpose.