SQL and Relational Theory, 2nd Edition

EXAMPLE 11: ALL OR ANY COMPARISONS

You probably know that SQL supports what are called generically ALL or ANY comparisons (or, more formally, quantified comparisons, but I prefer to avoid this term because of possible confusion with SQL’s EXISTS and UNIQUE operators). An ALL or ANY comparison is an expression of the form rx θ tsq, where:

• rx is a row expression.

• tsq is a table subquery. (Subqueries of all kinds are discussed further in Chapter 12.)

• θ is any of the usual scalar comparison operators supported in SQL (“=”, “<>”, “<”, “<=”, “>”, “>=”) followed by one of the keywords ALL, ANY, or SOME. (As mentioned in Chapter 7, in a footnote, SOME is just an alternative spelling for ANY in this context.)

The semantics are as follows:

• An ALL comparison returns TRUE if and only if the corresponding comparison without the ALL returns TRUE for all of the rows in the table represented by tsq. If that table is empty, the ALL comparison returns TRUE.^[162]

• An ANY comparison returns TRUE if and only if the corresponding comparison without the ANY returns TRUE for at least one of the rows in the table represented by tsq. If that table is empty, the ANY comparison returns FALSE.

Here’s an example (“Get part names for parts whose weight is greater than that of every blue part”):

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  PX.WEIGHT >ALL ( SELECT PY.WEIGHT
                             FROM   P AS PY
                             WHERE  PY.COLOR = 'Blue' )

Result:

PNAME

Bolt

Screw

Cog

As this example suggests, the “row expression” rx in the ALL or ANY comparison rx θ