07 - Advanced SQL: Subqueries, Set Operations & Views

🎯 Learning Objectives

• Write nested subqueries using IN, ANY, ALL, EXISTS, UNIQUE
• Use set operations UNION, INTERSECT, and EXCEPT (with/without ALL)
• Create and use views (virtual tables)
• Write WITH (CTE) queries for temporary view definitions

📋 Prerequisites

• 06 - SQL Aggregation — GROUP BY, HAVING, aggregate functions
• 05 - SQL Queries — Basic SELECT-FROM-WHERE

📖 Core Content

7.1 Intuition: Queries Within Queries

• "Find instructors whose salary is greater than the average" — you need the average first
• "Find departments that have at least one instructor earning > 90000"
• "Find courses that every student has taken" Subqueries (inner queries nested inside outer queries) solve these problems elegantly.

Why This Matters: Subqueries let you express complex conditions that would be impossible with a single flat SELECT. They're essential for real-world analytics.

7.2 Subqueries with IN

sql
-- Find instructors who teach in departments with budget > 100000
SELECT name
FROM instructor
WHERE dept_name IN (
    SELECT dept_name
    FROM department
    WHERE budget > 100000
);

NOT IN

sql
-- Find instructors NOT in departments with budget > 100000
SELECT name
FROM instructor
WHERE dept_name NOT IN (
    SELECT dept_name
    FROM department
    WHERE budget > 100000
);

Warning: If the subquery result contains NULL, NOT IN returns no rows! (Because X NOT IN (NULL, 'CS', 'Math') evaluates to UNKNOWN — NULL comparison always yields UNKNOWN.)

7.3 Subqueries with ANY and ALL

ANY (some)

sql
-- Find instructors whose salary is greater than SOME instructor in Biology
SELECT name
FROM instructor
WHERE salary > SOME (
    SELECT salary
    FROM instructor
    WHERE dept_name = 'Biology'
);

• = SOME ≡ IN
• <> SOME ≠ NOT IN (important distinction!)

ALL

sql
-- Find the instructor(s) with the highest salary
SELECT name
FROM instructor
WHERE salary >= ALL (
    SELECT salary
    FROM instructor
);

• <> ALL ≡ NOT IN
• = ALL ≠ IN (unless the subquery has only one distinct value)

7.4 Subqueries with EXISTS

sql
-- Find departments that have at least one instructor
SELECT dept_name
FROM department D
WHERE EXISTS (
    SELECT *
    FROM instructor I
    WHERE I.dept_name = D.dept_name
);

NOT EXISTS

sql
-- Find departments with NO instructors
SELECT dept_name
FROM department D
WHERE NOT EXISTS (
    SELECT *
    FROM instructor I
    WHERE I.dept_name = D.dept_name
);

EXISTS for Relational Division

sql
SELECT S.ID, S.name
FROM student S
WHERE NOT EXISTS (
    -- Courses in CS that this student has NOT taken
    (SELECT course_id FROM course WHERE dept_name = 'Comp. Sci.')
    EXCEPT
    (SELECT course_id FROM takes WHERE takes.ID = S.ID)
);

7.5 Subqueries with UNIQUE

sql
-- Find courses that have only one section (no duplicates in offering)
SELECT course_id
FROM course C
WHERE UNIQUE (
    SELECT course_id
    FROM section S
    WHERE S.course_id = C.course_id
);

7.6 Set Operations: UNION, INTERSECT, EXCEPT

UNION

sql
-- Courses taught in Fall 2017 or Spring 2018
(SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)
UNION
(SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018);

INTERSECT

sql
-- Courses taught in both Fall 2017 AND Spring 2018
(SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)
INTERSECT
(SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018);

EXCEPT (also called MINUS)

sql
-- Courses taught in Fall 2017 but NOT in Spring 2018
(SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)
EXCEPT
(SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018);

ALL Variants

7.7 WITH Clause (CTE — Common Table Expression)

sql
-- Find all departments with above-average budget
WITH dept_avg AS (
    SELECT AVG(budget) AS avg_budget FROM department
)
SELECT dept_name, budget
FROM department, dept_avg
WHERE department.budget > dept_avg.avg_budget;

Multiple CTEs

sql
-- Find the department with the maximum total salary
WITH dept_total(dept_name, total_salary) AS (
    SELECT dept_name, SUM(salary)
    FROM instructor
    GROUP BY dept_name
),
max_total(max_salary) AS (
    SELECT MAX(total_salary) FROM dept_total
)
SELECT dept_name, total_salary
FROM dept_total, max_total
WHERE dept_total.total_salary = max_total.max_salary;

7.8 Views

sql
CREATE VIEW faculty AS
SELECT ID, name, dept_name
FROM instructor;

sql
SELECT * FROM faculty WHERE dept_name = 'Comp. Sci.';

Materialized Views

sql
CREATE MATERIALIZED VIEW dept_salary_stats AS
SELECT dept_name, COUNT(*), AVG(salary)
FROM instructor
GROUP BY dept_name;

• Pros: Fast access (no recomputation needed)
• Cons: Must be refreshed when underlying data changes

7.9 Worked Examples

Example 1: Find instructors with salary > average (correlated)

sql
SELECT name, salary
FROM instructor I
WHERE salary > (
    SELECT AVG(salary)
    FROM instructor
    WHERE dept_name = I.dept_name  -- correlated: same department
);

Example 2: Using WITH for readability

sql
SELECT dept_name
FROM instructor
WHERE salary > (SELECT AVG(salary) FROM instructor)
ORDER BY dept_name;

sql
WITH overall_avg AS (
    SELECT AVG(salary) AS avg_sal FROM instructor
)
SELECT dept_name
FROM instructor, overall_avg
WHERE salary > overall_avg.avg_sal
ORDER BY dept_name;

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: NOT IN with NULL

Pitfall 2: Confusing = ANY with = ALL

Pitfall 3: Forgetting UNION removes duplicates

📝 Practice Questions

Q1. Write a query using EXISTS to find departments that have at least one instructor.

Answer

sql
SELECT dept_name
FROM department D
WHERE EXISTS (
    SELECT 1 FROM instructor I WHERE I.dept_name = D.dept_name
);

SELECT 1 is an optimization — we don't care about the actual data, just existence.

Q2. Find all courses taught in Fall 2017 but not in Spring 2018, using EXCEPT.

Answer

sql
(SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)
EXCEPT
(SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018);

Q3. What is the difference between UNION and UNION ALL?

Answer

• UNION: Removes duplicate tuples from the result (slower, but cleaner)
• UNION ALL: Preserves all duplicates (faster)

If a tuple appears m times in the first query and n times in the second:

• UNION: appears once
• UNION ALL: appears m + n times

Q4. Why does NOT IN fail with NULL values? How do you fix it?

Answer

X NOT IN (NULL, 'A', 'B') is equivalent to X <> NULL AND X <> 'A' AND X <> 'B'. Since X <> NULL is always UNKNOWN (regardless of X), the entire expression becomes UNKNOWN, and the WHERE clause treats it as FALSE.

Fix: Use NOT EXISTS:

sql
SELECT name FROM instructor I
WHERE NOT EXISTS (
    SELECT 1 FROM department D
    WHERE D.dept_name = I.dept_name AND D.budget < 50000
);

Q5. Write a query using WITH to find departments with above-average number of instructors.

Answer

sql
WITH dept_count AS (
    SELECT dept_name, COUNT(*) AS num_instructors
    FROM instructor
    GROUP BY dept_name
),
avg_count AS (
    SELECT AVG(num_instructors) AS avg_instructors FROM dept_count
)
SELECT dept_name, num_instructors
FROM dept_count, avg_count
WHERE dept_count.num_instructors > avg_count.avg_instructors;

Q6. What is a view in SQL? When would you use one?

Answer

A view is a virtual table defined by a query. It stores the query definition, not the data.

Use when:

• Security: Hide sensitive columns (e.g., salary) from certain users
• Simplicity: Provide a simplified interface to complex joins
• Consistency: Ensure everyone uses the same query logic

Q7. Write a query to find students who have taken ALL courses offered by the CS department.

Answer

sql
SELECT S.ID, S.name
FROM student S
WHERE NOT EXISTS (
    (SELECT course_id FROM course WHERE dept_name = 'Comp. Sci.')
    EXCEPT
    (SELECT T.course_id FROM takes T WHERE T.ID = S.ID)
);

This uses double negation: find students for whom there does NOT exist a CS course they have NOT taken.

Q8. What is the execution order of a query with subqueries?

Answer

For uncorrelated subqueries: The inner query runs first, produces a result, and the outer query uses that result.

For correlated subqueries: For each tuple in the outer query, the inner query is re-evaluated using the current outer tuple's values. This is like a nested loop.

Correlated subqueries can be slower because they run the inner query many times.

🔗 Cross-References

• Next Topic: 08 - SQL Joins
• Previous Topic: 06 - SQL Aggregation
• Related: BSMS2001 (BDM) — Complex business queries
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 3 (SQL), Chapter 4 (Intermediate SQL) Join Discord Previous06 - SQL AggregationNext08 - SQL Joins