Set Operations & Subqueries

Lecture 4 · combining query results with set operations, and nesting a query inside another — the most powerful (and most examined) part of SQL.

Set operations

union

in either query

reads as “or”

intersect

in both queries

reads as “and”

except

in the first, not the second

reads as “but not”

Run against this courseAllocation table:

ID	c_id	sec_id	semester
1	cse100	1	Fall
3	cse303	1	Spring
5	cse400	5	Autumn
4	eee100	2	Fall
6	bba200	3	Fall
1	cse100	6	Spring

6 tuples · 4 attributes

Task

Ran in Fall OR Spring

sql

(select c_id from courseAllocation
   where semester = 'Fall')
union
(select c_id from courseAllocation
   where semester = 'Spring');

↓ Result

c_id
cse100
eee100
bba200
cse303

4 tuples · 1 attribute

Task

Ran in Fall AND Spring

sql

(... where semester = 'Fall')
intersect
(... where semester = 'Spring');

↓ Result

c_id
cse100

1 tuple · 1 attribute

Task

Fall BUT NOT Spring

sql

(... where semester = 'Fall')
except
(... where semester = 'Spring');

↓ Result

c_id
eee100
bba200

2 tuples · 1 attribute

All three auto-remove duplicates. Keep them with the multiset forms union all / intersect all / except all: a tuple m× in r and n× in s appears m+n (union all), min(m,n) (intersect all), max(0, m−n) (except all) times.

Nested subqueries

A subquery is a select–from–where nested inside another query — usable in the where, from, or select clause.

In the WHERE clause — set membership

-- courses in Fall AND Spring   (same as intersect)
SELECT distinct c_id FROM courseAllocation
WHERE semester = 'Fall' AND c_id IN
      (SELECT c_id FROM courseAllocation WHERE semester = 'Spring');

-- courses in Fall but NOT Spring   (same as except)
... WHERE semester = 'Fall' AND c_id NOT IN (SELECT c_id ... 'Spring');

in ≡ intersect / set membership · not in ≡ except. Handy equivalences to quote in the exam.

Set comparison — SOME and ALL

Form	True when…	Equivalent
`> some`	greater than at least one value	—
`= some`	equals at least one	`in`
`> all`	greater than every value	—
`≠ all`	differs from every value	`not in`

-- salary greater than SOME (at least one) CSE instructor
SELECT name FROM instructor
WHERE salary > some (SELECT salary FROM instructor WHERE dept_name = 'CSE');

-- salary greater than ALL CSE instructors
WHERE salary > all (SELECT salary FROM instructor WHERE dept_name = 'CSE');

EXISTS / NOT EXISTS — empty-relation test

exists r → true if the subquery returns any row.
not exists r → true if the subquery is empty.

-- students who took ALL Biology courses:
-- no Biology course is missing from their record
SELECT distinct S.ID, S.name
FROM student AS S
WHERE NOT EXISTS (
  (SELECT course_id FROM course WHERE dept_name = 'Biology')
  EXCEPT
  (SELECT T.course_id FROM takes AS T WHERE S.ID = T.ID));

The “for all” trick: X − Y = ∅ ⟺ X ⊆ Y. “Took every Biology course” = “no Biology course is left over after removing the ones they took”.

Subquery in the FROM clause

SELECT dept_name, avg_salary
FROM ( SELECT dept_name, avg(salary) AS avg_salary
       FROM instructor GROUP BY dept_name )
WHERE avg_salary > 15000;          -- no HAVING needed

Scalar subquery (in SELECT)

SELECT ID, full_name,
       (SELECT count(*) FROM instructor
        WHERE department.dept_name = instructor.dept_name) AS num_instructors
FROM department;      -- must return exactly one value per row

The WITH clause (temporary relation)

WITH max_budget (value) AS (SELECT max(budget) FROM department)
SELECT department.name
FROM   department, max_budget
WHERE  department.budget = max_budget.value;

WITH names a temporary result you can reuse — great for multi-step questions (e.g. departments whose total salary beats the average of all department totals).

Apply all of this on the exam: Midterm Part 4 — SQL (solved) →