Nested Subqueries

A subquery is simply a select-from-where block placed inside another query. SQL lets you nest subqueries in the WHERE, FROM, and SELECT clauses.

select : Must return a single scalar value.
from : Can return any valid relation (table).
where : Used in expressions like Attribute <operation> (subquery).

All examples on this page run on the course datasets: press Run on any query to see its result.

Subqueries in the `WHERE` Clause

Typically used to test set membership, set comparison, and set cardinality.

Set Membership: `IN` / `NOT IN`

Example: Find courses offered in Fall 2009 and in Spring 2008.

select distinct course_id
from section
where semester = 'Fall' and year = 2009 and
      course_id in (select course_id
                    from section
                    where semester = 'Spring' and year = 2008);

Swap in for not in to find courses offered in Fall 2009 but not in Spring 2008.

Set Comparison: `SOME` and `ALL`

Clause	Meaning	Logic
`some`	True if comparison holds for at least one row in subquery	\(\exists\, t \in r : F \langle\text{comp}\rangle\, t\)
`all`	True if comparison holds for every row in subquery	\(\forall\, t \in r : F \langle\text{comp}\rangle\, t\)

Note

<comp> can be: <, ≤, >, ≥, =, ≠

Example: Instructors earning more than some instructor in Biology:

select name, salary
from instructor
where salary > some (select salary
                     from instructor
                     where dept_name = 'Biology');

Example: Instructors earning more than all instructors in Biology:

select name, salary
from instructor
where salary > all (select salary
                    from instructor
                    where dept_name = 'Biology');

How the result is decided. Biology has two salaries: 45538.32 and 77036.18.

Instructor	salary	> some	> all
Gutierrez	45310.53	✗	✗
Murata	61387.56	✓ (beats 45538.32)	✗
Romero	79070.08	✓	✓ (beats both)

Test for Empty Sets: `EXISTS`

exists r is true if r is non-empty.
not exists r is true if r is empty.

Example: Find courses offered in both Fall 2009 and Spring 2008, using a correlated subquery (inner query refers to the outer query’s variable).

select course_id
from section as S
where semester = 'Fall' and year = 2009 and
      exists (select *
              from section as T
              where semester = 'Spring' and year = 2008
                    and S.course_id = T.course_id);

S is the correlation name (outer query variable)
The inner query referencing S is the correlated subquery

take a row S

a Fall 2009 section

→

run the subquery

Spring 2008 rows with the same course_id

→

non-empty?

exists is true: keep S

Example: Find teams that have played against all other teams (uses not exists + set difference logic):

select t.name
from teams as t
where not exists (
    (select team_id from teams where team_id <> t.team_id)
    except
    (select guest_team_id from matches where host_team_id = t.team_id
     union
     select host_team_id from matches where guest_team_id = t.team_id)
);

For each team, “all other teams” minus “teams it has played” must be empty:

Team	Other teams not yet played	Kept?
Amigos	none (empty)	✓
Thunder	Rainbow	✗
Rainbow	Thunder, All Stars, Arawali	✗

select t.name
from teams as t
where (select count(*)
       from matches as m
       where m.host_team_id = t.team_id) <= 1;

Subqueries in the FROM Clause

A subquery can stand in for a table, letting you filter on an aggregate without HAVING.

Example: Average salary of departments where average salary exceeds 90,000:

select dept_name, avg_salary
from (select dept_name, round(avg(salary), 2) as avg_salary
      from instructor
      group by dept_name) as dept_avg
where avg_salary > 90000;

inner query

one row per department

→

as dept_avg

name the result

→

outer where

keep avg_salary > 90000

The `WITH` Clause

with defines a temporary, named result (like a scratchpad table) visible only to the query that follows.

Example: Find the name of the department that has the maximum budget.

with max_budget(value) as
    (select max(budget) from department)
select department.dept_name
from department, max_budget
where department.budget = max_budget.value;

Multiple with blocks can be chained for multi-step logic:

Example: Find the names of the departments whose total salary expense is greater than the average total salary expense across all departments.

dept_total

total salary per department

→

dept_total_avg

average of those totals

→

final select

departments above the average

with dept_total(dept_name, value) as
    (select dept_name, sum(salary)
     from instructor
     group by dept_name),
dept_total_avg(value) as
    (select avg(value) from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;

Subqueries in the SELECT Clause

A scalar subquery returns exactly one value, used wherever a single value is expected.

select dept_name,
       (select count(*)
        from instructor
        where department.dept_name = instructor.dept_name) as num_instructors
from department;

Note

If the subquery returns more than one row, this causes a runtime error.

Modifying the Database

Database modificationdelete · insert · update

DELETEremove tuples

INSERTadd tuples

UPDATEchange values

These statements change the dataset. Each example is wrapped in begin; … rollback; so the data is restored after you run it.

Deletion

begin;
-- Delete all instructors
delete from instructor;

-- Delete instructors from one department
delete from instructor where dept_name = 'Finance';

-- Delete instructors in departments located in a specific building
delete from instructor
where dept_name in (select dept_name
                    from department
                    where building = 'Taylor');
rollback;

Tricky case: Delete instructors earning less than the average salary.

begin;
delete from instructor
where salary < (select avg(salary) from instructor);
select count(*) from instructor;
rollback;

Note

As rows are deleted, the average changes! Solution: (1) computing the average and identifying all rows to delete first, then (2) deleting them — without recomputing mid-way.

phase 1

compute the average once

→

phase 1

mark every row below it

→

phase 2

delete the marked rows

Insertion

begin;
insert into course
values ('CS-437', 'Database Systems', 'Comp. Sci.', 4);

-- with tot_cred set to null
insert into student
values ('3003', 'Green', 'Finance', null);

-- insert from another query's results
insert into student
select id, name, dept_name, 0
from instructor
where id not in (select id from student);
rollback;

Note

The entire select-from-where is evaluated before any row is inserted — otherwise insert into table1 select * from table1 would loop infinitely.

Updates

Conditional updates — give a 3% raise above 100,000, 5% below (order matters!):

begin;
update instructor set salary = salary * 1.03 where salary > 100000;
update instructor set salary = salary * 1.05 where salary <= 100000;
rollback;

Cleaner with a case statement (single pass, no ordering issues):

begin;
update instructor
set salary = case
    when salary <= 100000 then salary * 1.05
    else salary * 1.03
end;
rollback;

Updates with scalar subqueries — recompute total credits per student:

begin;
update student S
set tot_cred = (select sum(credits)
                from takes, course
                where takes.course_id = course.course_id and
                      S.id = takes.id and
                      takes.grade <> 'F' and
                      takes.grade is not null);
rollback;

(Find the total credits earned by each student (excluding failed or uncollected grades) and update their record in the student table.)

Subqueries in the WHERE Clause

Set Membership: IN / NOT IN

Set Comparison: SOME and ALL

Test for Empty Sets: EXISTS

Subqueries in the FROM Clause

The WITH Clause

Subqueries in the SELECT Clause

Modifying the Database

Deletion

Insertion

Updates

Subqueries in the `WHERE` Clause

Set Membership: `IN` / `NOT IN`

Set Comparison: `SOME` and `ALL`

Test for Empty Sets: `EXISTS`

The `WITH` Clause