Advanced JOINs and Subqueries
What you'll build
A set of advanced SQL queries against an employee/department schema, including self-joins for hierarchies, anti-joins for finding missing records, and correlated subqueries for row-by-row comparisons.
Goals
- Write self-joins for hierarchical data
- Use anti-joins (LEFT JOIN + IS NULL) to find unmatched rows
- Write correlated and non-correlated subqueries
- Use EXISTS and NOT EXISTS efficiently
Prerequisites
- INNER and LEFT JOIN basics
- WHERE clause filtering
- Subquery basics
Steps
- 1
Set up the schema
Create an employees table with a self-referencing manager_id column to model an org hierarchy.
CodeCREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER REFERENCES employees(id), salary INTEGER NOT NULL, dept TEXT NOT NULL ); INSERT INTO employees (name, manager_id, salary, dept) VALUES ('CEO', NULL, 500000, 'Exec'), ('VP Eng', 1, 300000, 'Eng'), ('VP Sales', 1, 280000, 'Sales'), ('Dev Lead', 2, 200000, 'Eng'), ('Senior Dev', 4, 150000, 'Eng'), ('Junior Dev', 4, 90000, 'Eng'), ('Sales Rep', 3, 100000, 'Sales');Expected result
Seven employees inserted with a management hierarchy.
- 2
Self-join for manager names
Join employees to itself to find each employee's manager name. A self-join treats one table as two by using different aliases.
CodeSELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id ORDER BY e.id;Expected result
Each employee row shows their manager's name; CEO's manager is NULL.
- 3
Anti-join: employees without direct reports
An anti-join finds rows in one table that have NO match in another. Use LEFT JOIN + IS NULL to find employees who are not anyone's manager.
CodeSELECT e.name FROM employees e LEFT JOIN employees r ON r.manager_id = e.id WHERE r.id IS NULL ORDER BY e.name;Expected result
Returns 'Senior Dev', 'Junior Dev', and 'Sales Rep' — the leaf employees.
- 4
Correlated subquery: above-average salary
A correlated subquery references the outer query. Find employees who earn more than the average salary in their own department.
CodeSELECT name, salary, dept FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e.dept ) ORDER BY dept, salary DESC;Expected result
Returns employees earning above their department average — e.g., Dev Lead in Eng.
- 5
EXISTS for dependent records
Use EXISTS to check for the presence of related rows. Find departments that have at least one employee earning over 150000.
CodeSELECT DISTINCT dept FROM departments d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.dept = d.name AND e.salary > 150000 );Expected result
Returns 'Eng' and 'Exec' — departments with high earners.
Try it yourself
-- Self-join: find each employee's manager
SELECT e.name, ??? AS manager
FROM employees e
??? JOIN employees m ON ???;SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;What you learned
You mastered advanced JOIN patterns — self-joins for hierarchies, anti-joins for finding gaps, and correlated subqueries for row-level comparisons. These are essential for complex reporting queries.
Related Resources
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.