Which instructors earn more than every instructor in Math?
sql
SELECT name, salary
FROM instructor
WHERE salary > ALL (SELECT salary FROM instructor WHERE dept_name = 'Math');Math salaries are 81000 and 74000. > ALL keeps the salaries above both.
Which earn more than at least one instructor in Math?
sql
SELECT name, salary
FROM instructor
WHERE salary > ANY (SELECT salary FROM instructor WHERE dept_name = 'Math');> ANY keeps the salaries above 74000.
| Condition | True when |
|---|---|
x > ALL (subquery) | x is greater than every value |
x > ANY (subquery) | x is greater than at least one value |
x > SOME (subquery) | the same as ANY |
- Any comparison works:
=,<>,<,>=and the others. = ANYis the same asIN.<> ALLis the same asNOT IN, with the same NULL trap.- The subquery returns one column.
An empty list
When the subquery returns no rows, ALL is TRUE and ANY is FALSE. Biology has no instructors, so salary > ALL (salaries in Biology) keeps every instructor.