Skip to content

Advanced JOINs and Subqueries

Medium ~35 min

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. 1

    Set up the schema

    Create an employees table with a self-referencing manager_id column to model an org hierarchy.

    Code
    CREATE 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. 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.

    Code
    SELECT 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. 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.

    Code
    SELECT 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. 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.

    Code
    SELECT 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. 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.

    Code
    SELECT 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

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 Lessons

  • JOINs

    Combining tables with JOINs.

Related Glossary

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.

View errors

← Back to topic