Most joins connect two different tables. Two special cases come up often in interviews and real reports: the self join and the cross join.
Self join: a table joined to itself
A self join is a normal join where both sides are the same table, given two different aliases.
Example 1: employees and their managers
A fictional software firm, CodeCraft, stores everyone in one employees table, with manager_id pointing to another employee's emp_id.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;The LEFT JOIN keeps the CEO, who has no manager.
Example 2: employees earning more than their manager
SELECT e.name, 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;Example 3: pairs within a group
Pairs of employees in the same department, each pair listed once:
SELECT a.name AS person_1, b.name AS person_2, a.dept
FROM employees a
JOIN employees b ON a.dept = b.dept AND a.emp_id < b.emp_id;The a.emp_id < b.emp_id condition removes self-pairs (Asha–Asha) and mirror duplicates (Asha–Ravi and Ravi–Asha).
Example 4: comparing rows over time
Before window functions were common, analysts used self joins to compare each month with the previous one. Today LAG is usually cleaner, but the self join version is still a common interview question:
SELECT cur.month, cur.revenue, prev.revenue AS prev_revenue
FROM monthly cur
LEFT JOIN monthly prev ON prev.month = cur.month - INTERVAL '1 month';Cross join: every row with every row
A cross join pairs each row of the first table with each row of the second. Three stores and seven dates give 21 rows.
SELECT s.store_id, d.date
FROM stores s
CROSS JOIN calendar d;Why that is useful: filling gaps
Sales tables only have rows for days with sales. To show zero on days with none, build the full store × date grid, then LEFT JOIN actual sales onto it:
SELECT g.store_id, g.date, COALESCE(SUM(s.amount), 0) AS sales
FROM (SELECT s.store_id, d.date FROM stores s CROSS JOIN calendar d) g
LEFT JOIN sales s ON s.store_id = g.store_id AND s.sale_date = g.date
GROUP BY g.store_id, g.date;Other uses
- Every product × every size combination for a catalogue.
- Every team × every match-day for a fixture list.
- Comparing each row with a single-row summary (such as overall average) by cross joining that one row.
Be careful
A cross join of two large tables explodes in size: 10,000 rows × 10,000 rows is 100 million rows. An accidental cross join, from a forgotten join condition, is a classic cause of slow queries and wildly inflated totals.
Practise
Our self and cross join set has the manager, pairs and grid patterns, checked instantly.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: