Lecture 08: Subqueries & CTEs

1. What is a Subquery?

A subquery is a nested SELECT statement embedded within another SQL query. Subqueries appear in three primary locations:

1. In WHERE / HAVING: As a dynamic filter condition.
2. In FROM: As a temporary derived table.
3. In SELECT: As a scalar expression evaluated per row.

2. Scalar Subqueries

A scalar subquery returns exactly one row and one column (a single atomic value).

-- Find all instructors who earn more than the university average salary
SELECT name, salary
FROM instructor
WHERE salary > (SELECT AVG(salary) FROM instructor);

Note: If a scalar subquery returns more than 1 row at runtime, the database will abort with a runtime error:
ERROR: more than one row returned by a subquery used as an expression.

3. Subqueries in WHERE: IN and NOT IN

-- Find students who have taken courses in the 'Comp. Sci.' department
SELECT DISTINCT name 
FROM student 
WHERE id IN (
    SELECT id 
    FROM takes 
    WHERE course_id IN (
        SELECT course_id 
        FROM course 
        WHERE dept_name = 'Comp. Sci.'
    )
);

4. The Dangerous NOT IN + NULL Trap

Why Does This Happen?

Consider testing whether val NOT IN (10, 20, NULL). Under Three-Valued Logic, SQL expands this into:

val != 10   AND   val != 20   AND   val != NULL

Since any comparison with NULL yields UNKNOWN:

TRUE   AND   TRUE   AND   UNKNOWN => UNKNOWN

In SQL, a WHERE clause only passes rows where the condition is strictly TRUE. Because the condition evaluates to UNKNOWN, every single row is discarded!

How to Prevent the Trap:

1. Always filter out NULLs in the subquery:

   WHERE id NOT IN (SELECT advisor_id FROM student WHERE advisor_id IS NOT NULL)

2. Or use NOT EXISTS instead (which is immune to NULL traps):

   WHERE NOT EXISTS (SELECT 1 FROM student WHERE student.advisor_id = instructor.id)

5. Common Table Expressions (CTEs): WITH

A CTE creates a named, temporary result set that exists only during the execution of a single query. It dramatically improves readability over deeply nested subqueries:

WITH dept_stats AS (
    SELECT 
        dept_name, 
        AVG(salary) AS avg_sal,
        COUNT(*) AS faculty_count
    FROM instructor
    GROUP BY dept_name
),
high_paying_depts AS (
    SELECT dept_name 
    FROM dept_stats 
    WHERE avg_sal > 75000
)
SELECT i.name, i.salary, i.dept_name, s.avg_sal
FROM instructor i
JOIN dept_stats s ON i.dept_name = s.dept_name
WHERE i.dept_name IN (SELECT dept_name FROM high_paying_depts);

6. Topic Practice Questions

SELECT course_id, title 
FROM course 
WHERE course_id NOT IN (
    SELECT course_id 
    FROM section 
    WHERE year = 2025 AND course_id IS NOT NULL
);

WITH dept_averages AS (
    SELECT dept_name, AVG(salary) AS avg_salary
    FROM instructor
    GROUP BY dept_name
)
SELECT i.id, i.name, i.salary, i.dept_name, d.avg_salary
FROM instructor i
JOIN dept_averages d ON i.dept_name = d.dept_name
WHERE i.salary > d.avg_salary;