Nested And Set Queries¶

Nested Queries¶

in

SELECT A1, A2, ... An
FROM r1, r2, ... rn
WHERE P

one can replace \(r_i\) with any valid subquery
an operation can be used on a subquery for the predicate in WHERE
\(A_i\) can be replaced with any subquery that generates a single value

IN¶

If a query returns a set (i.e., single column), we can check if a member is present in it using the IN keyword

Example¶

SELECT course_ID
FROM course_offerings
WHERE course_year=2018 and course_ID in
    (SELECT course_ID
     FROM course_offerings
     WHERE course_year=2017)

Some, All clauses¶

The some clause selects all elements that fulfil a condition with some elements in a set
The all clause selects all elements that fulfil a condition with all elements of a set

Example¶

SELECT name
FROM instructor
WHERE dept_name = 'Physics' AND
    salary > ALL (
        SELECT salary
        FROM instructor
        WHERE dept_name = 'Biology'
    )

This query selects physics professors who earn more than every biology professor

Exists, Not exists, Except clauses¶

SELECT course_ID
FROM section as S
WHERE 
    semester = 'Fall' 
    and course_year = 2017 
    and EXISTS (
        SELECT *
        FROM section as T
        WHERE
            semester = 'Spring'
            and course_year = 2018
            and S.course_ID = T.course_ID
    )

EXCEPT is just a set difference. A EXCEPT B returns A - B