Nested Subqueries

A subquery is a select-from-where query that is nested within another query.

A common use of subqueries is to perform tests for set membership (in/not in), set comparisons and set cardinality.

Ex. to get the names of instructors who get a salary greater than that of at least one instructor in the Biology department:

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

Ex. to get the names of instructors who get a salary greater than that of all the instructors in the Biology department:

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