SELF JOIN

A SELF JOIN joins a table to itself — the same table appears twice in the FROM clause under two different aliases. The database treats each alias as an independent copy, allowing rows within the same table to be related to or compared with each other. Without aliases, the database cannot distinguish which "copy" of the table each column reference belongs to, so aliases are mandatory.

The most common use case is hierarchical data stored in a single table — organisational charts where an employees table has a manager_id that references another row in the same table. To retrieve an employee alongside their manager's name, you join employees AS emp to employees AS mgr on emp.manager_id = mgr.employee_id. Both references go to the same physical table, but the aliases create two logical copies.

Other use cases: row-to-row comparison within the same table (finding customers in the same city by joining customers to itself on city = city and customer_id < customer_id), duplicate detection (same email or same name), product co-occurrence analysis (which products appear together in orders by joining order_items to itself on order_id), and sequential row comparison (comparing each order to the previous order for the same customer). The pattern to reach for: whenever you need to compare two rows of the same table, or when a foreign key in a table references the same table's primary key, SELF JOIN is the tool.

When joining a table to itself to compare all pairs of rows, each pair (A, B) would appear twice — once as (A, B) and once as (B, A) — plus each row would match itself (A, A). Adding a.primary_key < b.primary_key to the ON condition eliminates both problems in one constraint.

The less-than condition works because it enforces that the left alias always has a smaller ID than the right alias. For any two rows 1 and 2: only (1, 2) survives — (2, 1) is excluded because 2 < 1 is false. Self-matches (1, 1) are excluded because 1 < 1 is false. The result contains each unique pair exactly once.

Choose the condition based on what you need: use a.id < b.id to get each unordered pair once — use this when (A,B) and (B,A) represent the same pair and you only need one row. Use a.id <> b.id to exclude self-matches but keep both directions — use this when order matters, such as "find all pairs where employee A earns more than employee B" (A earns more than B is different from B earns more than A). Use a.id < b.id for symmetric relationships (same city, same salary, same category); use a.id <> b.id for directional relationships (earns more than, reports to).

Use a SELF JOIN on the employees table with two aliases — one for the employee row and one for the manager row. The join condition connects the employee's manager_id foreign key to the manager row's employee_id primary key: FROM employees AS emp JOIN employees AS mgr ON emp.manager_id = mgr.employee_id.

SELECT emp.employee_id, emp.first_name AS employee_name, emp.role, mgr.first_name AS manager_name, mgr.role AS manager_role FROM employees AS emp JOIN employees AS mgr ON emp.manager_id = mgr.employee_id ORDER BY emp.employee_id. This INNER JOIN returns only employees who have a manager — employees with NULL manager_id are excluded.

To include employees with no manager (top-level executives), change INNER JOIN to LEFT JOIN: FROM employees AS emp LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id. Employees with no manager appear with NULL in all manager columns. Use COALESCE(mgr.first_name, 'No manager') to display a meaningful label. For a three-level hierarchy, add a third alias: LEFT JOIN employees AS senior ON mgr.manager_id = senior.employee_id. Each additional level adds one more alias and one more LEFT JOIN. For unlimited depth, use recursive CTEs (WITH RECURSIVE) instead of fixed-depth self joins.

A self join retrieves manager data as additional columns in the same result row — you get employee columns and manager columns side by side. A correlated subquery in SELECT retrieves one value per employee row — useful for fetching a single manager attribute but not for fetching multiple manager attributes cleanly.

Self join for multiple attributes: SELECT emp.name, mgr.name AS manager, mgr.salary AS mgr_salary, mgr.role AS mgr_role FROM employees AS emp LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id. This fetches name, salary, and role from the manager row in one join. Clean and efficient — one table scan per alias.

Subquery for a single attribute: SELECT emp.name, (SELECT mgr.name FROM employees AS mgr WHERE mgr.employee_id = emp.manager_id) AS manager_name FROM employees AS emp. This is a correlated subquery — it executes once per employee row, which is O(n) subquery executions. For fetching one attribute it is readable, but for fetching multiple manager attributes it requires multiple correlated subqueries (one per attribute), each executing O(n) times — significantly less efficient than a single self join. The self join is the preferred approach for any query that needs more than one column from the related row. Use a correlated subquery only when you need one specific value and the join would be complex or unintuitive.

This requires comparing orders rows within the same table — specifically joining orders to itself on both customer conditions and date equality. The self join: FROM orders AS a JOIN orders AS b ON a.order_date = b.order_date AND a.customer_id <> b.customer_id AND a.order_id < b.order_id.

The three ON conditions serve different purposes. a.order_date = b.order_date finds orders on the same day. a.customer_id <> b.customer_id ensures we are comparing different customers — not the same customer's two orders (though you might want those too if the question is about same-customer same-day). a.order_id < b.order_id deduplicates pairs so (order 5, order 8) appears once, not as both (5,8) and (8,5).

The full query: SELECT a.customer_id AS customer_a, b.customer_id AS customer_b, a.order_date, a.order_id AS order_a, b.order_id AS order_b, a.total_amount AS amount_a, b.total_amount AS amount_b FROM orders AS a JOIN orders AS b ON a.order_date = b.order_date AND a.customer_id <> b.customer_id AND a.order_id < b.order_id ORDER BY a.order_date, customer_a. This can then be joined to the customers table using both customer_a and customer_b IDs to retrieve customer names. This pattern — cross-table pair detection through a junction table self join — also underlies product co-occurrence analysis (which products appear in the same order) by joining order_items to itself on order_id with product_id deduplication.

Cause: The ON condition is missing or too broad. Without a selective join condition, every row in alias 'a' matches every row in alias 'b' — producing a Cartesian product. With 20 employees, this gives 400 rows (20×20) instead of the expected 20. Forgetting the primary key deduplication condition (a.id < b.id) when doing pair comparison also inflates results by 2x.

Fix: Add the correct join condition. For hierarchy: ON emp.manager_id = mgr.employee_id — this is highly selective (each employee has at most one manager). For pair comparison: ON a.shared_column = b.shared_column AND a.id < b.id — both the shared value and the deduplication condition are required. Run SELECT COUNT(*) first to detect unexpected row counts before fetching all results.

Cause: Without table alias prefixes, the database cannot determine which copy of the table a column belongs to. In a self join, both aliases share all the same column names. SELECT employee_id without a prefix is ambiguous between emp.employee_id and mgr.employee_id.

Fix: Prefix every column reference with its alias: emp.employee_id, mgr.employee_id. In a self join, every single column reference must be prefixed — there are no unambiguous columns because both sides have identical column names. Use descriptive aliases (emp/mgr not e1/e2) so the prefix communicates meaning: mgr.salary is immediately clear; e2.salary is not.

Cause: INNER JOIN on the manager relationship excludes employees whose manager_id is NULL (employees with no manager). Top-level executives, founders, or any employee without a manager record are silently dropped from the result. This is a common mistake — the intent is to see all employees with their manager, but the result is only employees who have a manager.

Fix: Use LEFT JOIN instead of INNER JOIN: FROM employees AS emp LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id. All employees appear; manager columns are NULL for top-level employees. Wrap manager columns with COALESCE: COALESCE(mgr.first_name || ' ' || mgr.last_name, 'No manager') for clean display.

Cause: INNER JOIN between the manager and senior manager aliases excludes all employees whose manager has no manager. In a two-level organisation where most managers report to a top-level executive, INNER JOIN on the third level drops everyone whose manager is top-level (manager.manager_id IS NULL). The deeper the hierarchy chain with INNER JOINs, the more rows are dropped.

Fix: Use LEFT JOIN for every level beyond the first: LEFT JOIN employees AS mgr ON emp.manager_id = mgr.employee_id AND LEFT JOIN employees AS senior ON mgr.manager_id = senior.employee_id. INNER JOIN only the first level if the relationship is mandatory (every employee must have a manager). LEFT JOIN all subsequent levels so employees at the top of each sub-hierarchy are preserved with NULLs in the missing level columns.

Cause: The self join on order_items compares every (item, item) pair within the same order. With 100 orders each containing 5 items, there are 500 item rows. The self join on order_id produces 500×500 = 250,000 candidate pairs, filtered down to 1,000 unique pairs per order (5×4/2 = 10 pairs × 100 orders). On a real e-commerce table with millions of order items, this becomes billions of candidate pairs — too expensive.

Fix: Limit the scope with WHERE before the self join: restrict to a specific time window (order_date >= '2024-01-01'), a specific category, or a specific store. This reduces the order_items rows before the self join multiplies them. Also ensure order_id is indexed — the join ON oi1.order_id = oi2.order_id must use an index. For production co-occurrence analysis at scale, pre-aggregate pairs into a summary table using a batch job rather than computing them on the fly.

Write two queries using SELF JOIN: (1) A hierarchy query showing each employee's name, role, salary, their direct manager's name and salary, and a 'salary_relationship' column showing 'Higher than manager', 'Lower than manager', 'Same as manager', or 'No manager' as appropriate. Include all employees. Sort by department then salary descending. (2) A pair comparison query finding all pairs of products in the same category where both products are in stock and their price difference is less than ₹50. Show product_a name, product_b name, category, price_a, price_b, and price_difference. Sort by category and price_difference ascending.