Self-Joins and Anti-Joins

Two requests arrive on the same morning. Rania, the CEO, wants a clean list of who reports to whom before a reorganisation. Huda wants to know which customers signed up and never bought anything. They look unrelated, but both are about relationships between rows: one follows a link within a table, the other looks for links that are missing.

A table joined to itself

In employees, manager_id holds the employee_id of another row in the same table. To show each employee next to their manager’s name, you join employees to itself. The trick is two aliases, one per role:

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  e.title,
  m.first_name || ' ' || m.last_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON m.employee_id = e.manager_id
ORDER BY e.department, manager;

Read it as two copies of the table: e is the employee, m is the person they report to. The || operator concatenates text. A LEFT JOIN keeps Rania herself, whose manager_id is NULL; an inner join would quietly drop the CEO from the org chart, which is not a mistake you want to explain.

The same pattern works on customers.referred_by. Who brings in the most new customers?

SELECT
  r.customer_id,
  r.first_name || ' ' || r.last_name AS referrer,
  COUNT(*) AS customers_referred
FROM customers AS c
JOIN customers AS r ON r.customer_id = c.referred_by
GROUP BY r.customer_id
ORDER BY customers_referred DESC, r.customer_id
LIMIT 5;

Here c is the new customer and r the referrer. Leo Ibrahim referred three people; nobody referred more. Naming aliases after roles (e/m, c/r) rather than a/b is what keeps self-joins readable.

Self-joins only go one level at a time: employee to manager. “Everyone in Peter’s organisation, at any depth” needs a recursive query, which you’ll write in the next section.

Semi-joins: does a match exist?

Last lesson, WHERE order_id IN (SELECT order_id FROM support_tickets) found orders with at least one ticket, without the fan-out a join would cause. That’s a semi-join. EXISTS is the other common spelling:

SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

The subquery is correlated: it refers to c from the outer query, so it’s evaluated per customer. SELECT 1 is a convention; EXISTS only cares whether any row comes back, not what’s in it.

Anti-joins: who has no match?

Huda’s question is the opposite: customers with no orders. There are two reliable ways to write an anti-join. The first is NOT EXISTS:

SELECT
  strftime('%Y', c.signup_date) AS signup_year,
  COUNT(*) AS never_ordered
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
GROUP BY signup_year;

85 customers never ordered, and 52 of them signed up in 2024, at least a year before the data ends. That’s the actionable part for Huda: the 2025 sign-ups might still convert, the 2024 ones need a different campaign.

The second way is a LEFT JOIN that keeps only the unmatched rows:

SELECT COUNT(*) AS never_ordered
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Same 85. Test the right table’s key for NULL, because a key is never NULL on a real match. Both forms are standard and fast; most analysts prefer NOT EXISTS because it states the intent and can’t fan out.

Anti-joins with conditions

Real anti-joins usually carry a condition: not “never ordered”, but “hasn’t ordered recently”. The condition goes inside the NOT EXISTS subquery, where it narrows which orders count as a match. Here are customers who bought in 2024 but not at all in 2025, a classic lapsed-customer list:

SELECT COUNT(DISTINCT c.customer_id) AS lapsed_customers
FROM customers AS c
JOIN orders AS o24
  ON o24.customer_id = c.customer_id
 AND o24.order_date < '2025-01-01'
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o25
  WHERE o25.customer_id = c.customer_id
    AND o25.order_date >= '2025-01-01'
);

The join to 2024 orders fans out (a customer with five 2024 orders appears five times), so the query counts distinct customers. Putting the 2025 condition anywhere outside the subquery would change the meaning entirely, the same lesson as ON versus WHERE, one level deeper.

Which one to use

That completes the join toolkit. The next section stacks these building blocks into longer analyses, starting with a way to name intermediate results so a 40-line query still reads top to bottom.