SELF and CROSS joins

Part of the free LogicWiz SQL & Databases course, Module 5: Joins. The queries and practice run in the app on real Postgres.

Meher joined because Aarav sent her a referral link. In customers, her referred_by is 1, which is Aarav's own row in the same table. How do you print "Meher Kaur was referred by Aarav Sharma"?

You'll learn to

  • Join a table to itself with two aliases
  • Read manager and referral chains
  • Build every combination with CROSS JOIN
  • List pairs without duplicates

Learn

One table, two roles

A self join is an ordinary join where both sides are the same table. The trick is the aliases: give each copy a name that says its role.

  • c = the customer
  • r = the referrer

Then the ON line reads like English: "the referrer is the customer whose id is in my referred_by": r.customer_id = c.referred_by.

Who referred whom

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
JOIN customers r ON r.customer_id = c.referred_by
ORDER BY c.customer_id;

7 customers came through a referral. The other 5 have referred_by NULL, so the inner join drops them. Use LEFT JOIN to list everyone with NULL for "no referrer".

Managers in a company

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;

The company dataset has the same shape: manager_id points at another employee. This is the classic "employees who earn more than their manager". Priya Nair, a star sales executive, out-earns her head of sales.

The usual self-join bug is the direction. ON r.referred_by = c.customer_id is also valid SQL, but it means "r was referred by c", so every pair comes out reversed. Name your aliases by role and say the ON line aloud before you run it.

CROSS JOIN: every pair

A CROSS JOIN B pairs every row of A with every row of B, with no condition. 4 desserts × 2 drinks = 8 rows; 8 restaurants × 7 partners = 56.

Useful when you really want all combinations: combo meals, a grid of every city × every day for a report, every team against every other team. Dangerous when it happens by accident: a forgotten join condition on two big tables can produce millions of rows.

A combo menu

SELECT s.name AS starter, d.name AS drink, s.price + d.price AS combo_price
FROM menu_items s
CROSS JOIN menu_items d
WHERE s.category = 'Starter'
  AND d.category = 'Drink'
ORDER BY combo_price, starter, drink;

3 starters × 2 drinks = 6 combos. This is also a self join: one table used in two roles.

Pairs without repeats

SELECT a.name, b.name, a.city
FROM restaurants a
JOIN restaurants b ON b.city = a.city
                  AND a.restaurant_id < b.restaurant_id;

Restaurants in the same city. Without a.restaurant_id < b.restaurant_id you get each restaurant paired with itself, and every pair twice (A-B and B-A). < keeps each pair once.

Interview favourites: employees who earn more than their manager, each employee with their manager's name (LEFT self join so the CEO stays), and all pairs without duplicates (a.id < b.id). In exams, a cross join is the Cartesian product (×): its row count is the product of the two row counts.

In one line

A self join uses one table in two roles (c and r); CROSS JOIN makes every pair, m × n rows.