SQL Self Join for Joining a Table to Itself

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    manager_id INTEGER,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees VALUES (1, 'Sarah Chen', 'CEO', 'Executive', 250000, NULL);
INSERT INTO employees VALUES (2, 'Marcus Rivera', 'VP Engineering', 'Engineering', 180000, 1);
INSERT INTO employees VALUES (3, 'Linda Park', 'VP Marketing', 'Marketing', 175000, 1);
INSERT INTO employees VALUES (4, 'James Cooper', 'Senior Developer', 'Engineering', 120000, 2);
INSERT INTO employees VALUES (5, 'Aisha Patel', 'Senior Developer', 'Engineering', 125000, 2);
INSERT INTO employees VALUES (6, 'Tom Wilson', 'Junior Developer', 'Engineering', 75000, 4);
INSERT INTO employees VALUES (7, 'Nina Santos', 'Junior Developer', 'Engineering', 72000, 4);
INSERT INTO employees VALUES (8, 'Kevin Zhang', 'Marketing Manager', 'Marketing', 95000, 3);
INSERT INTO employees VALUES (9, 'Rachel Adams', 'Content Writer', 'Marketing', 65000, 8);
INSERT INTO employees VALUES (10, 'David Kim', 'SEO Specialist', 'Marketing', 70000, 8);

SELECT id, name, role, manager_id
FROM employees
ORDER BY id;

Sarah Chen (CEO)
├── Marcus Rivera (VP Engineering)
│   ├── James Cooper (Senior Developer)
│   │   ├── Tom Wilson (Junior Developer)
│   │   └── Nina Santos (Junior Developer)
│   └── Aisha Patel (Senior Developer)
└── Linda Park (VP Marketing)
    └── Kevin Zhang (Marketing Manager)
        ├── Rachel Adams (Content Writer)
        └── David Kim (SEO Specialist)

SELECT e.name AS employee,
       e.role,
       m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

FROM employees e                               -- First "copy": treat as the employee
INNER JOIN employees m ON e.manager_id = m.id  -- Second "copy": treat as the manager

SELECT e.name AS employee,
       e.role,
       COALESCE(m.name, 'No Manager (Top Level)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;

SELECT e.name AS employee,
       e.role,
       e.department,
       COALESCE(m.name, '/') AS manager,
       COALESCE(m.role, 'Top Level') AS manager_role
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.department, e.name;

SELECT e.name AS employee,
       e.role,
       e.salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE m.name = 'Marcus Rivera';

SELECT m.name AS manager,
       m.role,
       COUNT(e.id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name, m.role
HAVING COUNT(e.id) > 0
ORDER BY direct_reports DESC;

SELECT m.name AS manager,
       m.role AS manager_role,
       m.department,
       COUNT(e.id) AS direct_reports,
       ROUND(AVG(e.salary), 2) AS avg_report_salary,
       MIN(e.salary) AS min_report_salary,
       MAX(e.salary) AS max_report_salary
FROM employees m
INNER JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name, m.role, m.department
ORDER BY direct_reports DESC, avg_report_salary DESC;

SELECT e.name AS employee,
       e.role,
       m.name AS manager,
       gm.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id
ORDER BY gm.name, m.name, e.name;

SELECT e.name AS employee,
       m1.name AS manager,
       m2.name AS directors_manager,
       m3.name AS top_level
FROM employees e
LEFT JOIN employees m1 ON e.manager_id = m1.id
LEFT JOIN employees m2 ON m1.manager_id = m2.id
LEFT JOIN employees m3 ON m2.manager_id = m3.id
WHERE e.name = 'Tom Wilson';

SELECT e1.name AS employee_a,
       e2.name AS employee_b,
       e1.role,
       e1.salary AS salary_a,
       e2.salary AS salary_b,
       ABS(e1.salary - e2.salary) AS salary_difference
FROM employees e1
INNER JOIN employees e2 ON e1.role = e2.role AND e1.id < e2.id
ORDER BY e1.role, salary_difference DESC;

SELECT e1.name AS employee_a,
       e2.name AS employee_b,
       e1.department
FROM employees e1
INNER JOIN employees e2
    ON e1.department = e2.department
    AND e1.id < e2.id
WHERE e1.department = 'Engineering'
ORDER BY e1.name, e2.name;

SELECT p1.name AS product_a,
       p2.name AS product_b,
       cat.name AS category,
       p1.price AS price_a,
       p2.price AS price_b,
       ABS(p1.price - p2.price) AS price_gap
FROM products p1
INNER JOIN products p2
    ON p1.category_id = p2.category_id
    AND p1.id < p2.id
INNER JOIN categories cat ON p1.category_id = cat.id
WHERE ABS(p1.price - p2.price) < 20
ORDER BY price_gap ASC;

SELECT o1.id AS earlier_order,
       o2.id AS later_order,
       c.first_name || ' ' || c.last_name AS customer,
       o1.order_date AS first_date,
       o2.order_date AS second_date,
       o2.order_date - o1.order_date AS days_between
FROM orders o1
INNER JOIN orders o2
    ON o1.customer_id = o2.customer_id
    AND o1.order_date < o2.order_date
INNER JOIN customers c ON o1.customer_id = c.id
ORDER BY customer, o1.order_date;

SELECT e.name AS employee,
       e.role,
       e.salary AS employee_salary,
       m.salary AS manager_salary,
       e.salary - m.salary AS salary_difference,
       ROUND(e.salary * 100.0 / m.salary, 1) AS pct_of_manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
ORDER BY pct_of_manager DESC;

SELECT m.name AS manager,
       m.role,
       m.salary AS manager_salary,
       COUNT(e.id) AS team_size,
       SUM(e.salary) AS team_payroll,
       SUM(e.salary) + m.salary AS total_cost,
       ROUND(AVG(e.salary), 2) AS avg_team_salary
FROM employees m
INNER JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name, m.role, m.salary
ORDER BY total_cost DESC;

SELECT e.name AS employee,
       e.role AS employee_role,
       e.salary AS employee_salary,
       m.name AS manager,
       m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Temporarily give Aisha a raise above her VP
UPDATE employees SET salary = 185000 WHERE name = 'Aisha Patel';

SELECT e.name AS employee,
       e.salary AS employee_salary,
       m.name AS manager,
       m.salary AS manager_salary,
       e.salary - m.salary AS overpayment
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Reset Aisha's salary
UPDATE employees SET salary = 125000 WHERE name = 'Aisha Patel';

SELECT e.name AS employee,
       e.role,
       e.department
FROM employees e
LEFT JOIN employees report ON e.id = report.manager_id
WHERE report.id IS NULL
ORDER BY e.department, e.name;

SELECT p1.name AS current_product,
       p1.price AS current_price,
       p2.name AS upgrade_option,
       p2.price AS upgrade_price,
       p2.price - p1.price AS price_increase
FROM products p1
INNER JOIN products p2
    ON p1.category_id = p2.category_id
    AND p2.price > p1.price
WHERE p1.is_available = true
  AND p2.is_available = true
ORDER BY p1.name, price_increase ASC;

SELECT p1.name AS product,
       p1.price,
       p2.name AS next_cheapest_above,
       p2.price AS next_price
FROM products p1
INNER JOIN products p2
    ON p2.price > p1.price
WHERE p2.price = (
    SELECT MIN(p3.price)
    FROM products p3
    WHERE p3.price > p1.price
)
ORDER BY p1.price;

-- Wrong: Includes self-pairs (Tom Wilson compared with Tom Wilson)
SELECT e1.name, e2.name
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department;
-- Includes: Tom Wilson | Tom Wilson (meaningless)

-- Correct: Exclude self-pairs
SELECT e1.name, e2.name
FROM employees e1
INNER JOIN employees e2 ON e1.department = e2.department
WHERE e1.id != e2.id;
-- Or use e1.id < e2.id to also prevent reverse duplicates

-- Wrong: Cannot use the same alias twice
SELECT e.name, e.name
FROM employees e
INNER JOIN employees e ON e.manager_id = e.id;
-- ERROR: table name "e" specified more than once

-- Correct: Each instance needs a unique alias
SELECT e.name, m.name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

-- Wrong: Joining employee id to employee id (every row matches itself)
SELECT e.name, m.name
FROM employees e
INNER JOIN employees m ON e.id = m.id;
-- This just returns each employee paired with themselves

-- Correct: Join the foreign key to the primary key
SELECT e.name, m.name
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

SELECT e.name AS employee,
       e.department AS employee_dept,
       COALESCE(m.name, 'None') AS manager,
       COALESCE(m.department, 'N/A') AS manager_dept
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.department, e.name;

SELECT e1.name AS employee_a,
       e2.name AS employee_b,
       e1.department,
       e1.salary AS salary_a,
       e2.salary AS salary_b,
       ABS(e1.salary - e2.salary) AS salary_gap
FROM employees e1
INNER JOIN employees e2
    ON e1.department = e2.department
    AND e1.id < e2.id
WHERE ABS(e1.salary - e2.salary) < 10000
ORDER BY salary_gap;

SELECT e.name AS employee,
       e.role,
       m1.name AS manager,
       m2.name AS skip_level,
       m3.name AS top_level
FROM employees e
LEFT JOIN employees report ON e.id = report.manager_id
LEFT JOIN employees m1 ON e.manager_id = m1.id
LEFT JOIN employees m2 ON m1.manager_id = m2.id
LEFT JOIN employees m3 ON m2.manager_id = m3.id
WHERE report.id IS NULL
ORDER BY e.name;

SELECT p1.name AS product_a,
       p2.name AS product_b,
       cat.name AS category
FROM products p1
INNER JOIN products p2
    ON p1.category_id = p2.category_id
    AND p1.id < p2.id
INNER JOIN categories cat ON p1.category_id = cat.id
WHERE p1.is_available = true
  AND p2.is_available = true
  AND EXISTS (SELECT 1 FROM reviews WHERE product_id = p1.id)
  AND EXISTS (SELECT 1 FROM reviews WHERE product_id = p2.id)
ORDER BY cat.name, p1.name;

-- PostgreSQL syntax
SELECT m.name AS manager,
       m.role,
       STRING_AGG(e.name, ', ' ORDER BY e.name) AS direct_reports
FROM employees m
INNER JOIN employees e ON m.id = e.manager_id
GROUP BY m.id, m.name, m.role
ORDER BY m.name;

-- Remove the employees table if no longer needed
-- DROP TABLE IF EXISTS employees;