sql

🔷 SQL: Self-Joins

A self-join joins a table to itself. You need this when a row references another row in the SAME table.

-- Employees earning more than their manager
SELECT e.name AS employee, e.salary AS emp_salary,
       m.name AS manager, m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

The trick: You alias the same table twice (e and m) and treat them as two separate tables. The JOIN condition links the employee's manager_id to the manager's employee_id.

Another self-join pattern — consecutive events:

-- Users who made purchases on consecutive days
SELECT DISTINCT a.user_id
FROM purchases a
JOIN purchases b ON a.user_id = b.user_id
  AND b.purchase_date = a.purchase_date + INTERVAL '1 day';

Practice Questions

Q: Table flights has columns: flight_id, origin, destination, departure_time. Write a query to find all pairs of flights where you can connect (flight 1's destination = flight 2's origin, and flight 2 departs after flight 1 arrives).