Subqueries

Learning Focus

Use this lesson to write subqueries — queries nested inside other queries — for dynamic filtering and derived tables.

Types of Subqueries

Type	Location	Returns
Scalar	SELECT clause	Single value
Derived table	FROM clause	A table
Correlated	WHERE clause, references outer query	Per-row result
EXISTS	WHERE clause	Boolean

Scalar Subquery

-- Find employees earning above average salary
SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Derived Table (FROM Subquery)

-- Subquery in FROM acts as a virtual table
SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_averages
WHERE avg_salary > 75000;

IN / NOT IN with Subquery

-- Employees in departments where average salary > 80000
SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING AVG(salary) > 80000
);

EXISTS / NOT EXISTS

-- Employees who have at least one project assignment
SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM project_assignments pa WHERE pa.employee_id = e.id
);

-- Employees with NO project assignments
SELECT name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM project_assignments pa WHERE pa.employee_id = e.id
);

Correlated Subquery

-- Find employees earning more than their department's average
SELECT name, department, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department  -- references outer query
);

Subqueries vs CTEs

Aspect	Subquery	CTE
Readability	Lower (nested)	Higher (named)
Reuse	Must repeat	Reference by name
Recursion	Not supported	Supported
DuckDB performance	Equivalent	Equivalent for most cases

Common Pitfalls

Pitfall	Consequence	Prevention
Scalar subquery returns multiple rows	Error	Add LIMIT 1 or use MIN/MAX
NOT IN with NULL in results	Returns empty set	Use NOT EXISTS instead of NOT IN when NULLs possible
Correlated subquery on large tables	N×M execution	Replace with JOIN or window function

Quick Reference

-- Scalar
SELECT (SELECT MAX(salary) FROM employees);

-- Derived table
SELECT * FROM (SELECT col FROM t WHERE cond) sub;

-- IN subquery
WHERE col IN (SELECT col FROM t WHERE cond);

-- EXISTS
WHERE EXISTS (SELECT 1 FROM t WHERE t.id = outer.id);

What's Next

• Next: LATERAL Joins - Access outer query values inside joins.
• Module Overview - Return to this module index.