When the ALL special operator is used in a nested query, the test condition is true only if every record retrieved by the subselect satisfies the condition. The example nested query that follows illustrates how an ordinary relational operator is used in conjunction with the ALL special operator. This query retrieves the supplier numbers and part numbers of all parts supplied in at least as large a quantity as parts "P1" and "P6":
SELECT sno, pno
FROM partsupp
WHERE qty >= ALL
(SELECT qty
FROM partsupp
WHERE pno = "P1"
OR pno = "P6")
The result of the previous query appears below:
| sno | pno |
|---|---|
| S1 | P1 |
| S1 | P3 |
| S2 | P2 |
| S2 | P1 |
| S4 | P4 |
| S4 | P5 |