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:

The semantics are as follows:

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:

As this example suggests, the “row expression” rx in the ALL or ANY comparison rx θ tsq is often—almost always, in fact—just a simple scalar expression, in which case the scalar value denoted by that expression is effectively coerced to a row that contains just that scalar value. (Incidentally, note that even if rx doesn’t consist of a simple scalar expression but actually does denote a row of degree greater than one, θ can still be something other than “=” or “<>”, though the practice isn’t recommended. See Chapter 3 for further discussion of this point.)

Recommendation: Don’t use ALL or ANY comparisons—they’re error prone, and in any case their effect can always be achieved by other methods. As an illustration of the first point, consider the fact that a natural language formulation of the foregoing query might very well use any in place of every—“Get part names for parts whose weight is greater than that of any blue part”—which could lead to the incorrect use of >ANY in place of >ALL. As another example, illustrating both points, consider the following SQL expression:

     SELECT DISTINCT SNAME
     FROM   S
     WHERE  CITY <>ANY ( SELECT CITY FROM P )

This expression could easily be read as “Get names of suppliers whose city isn’t equal to any part city”—but that’s not what it means. Instead, it’s logically equivalent^[163] to the following (“Get names of suppliers where there’s at least one part in a different city”):

     SELECT DISTINCT SNAME
     FROM   S
     WHERE  EXISTS ( SELECT *
                     FROM   P
                     WHERE  P.CITY <> S.CITY )

Result:

In fact, ALL or ANY comparisons can always be transformed into equivalent expressions involving EXISTS, as the foregoing example suggests. They can also usually be transformed into expressions involving MAX or MIN—because certainly (e.g.) a value is greater than all of the values in some set if and only if it’s greater than the maximum value in that set—and expressions involving MAX and MIN are often easier to understand, intuitively speaking, than ALL or ANY comparisons. The table overleaf summarizes the possibilities in this regard. Note in particular from the table that =ANY and <>ALL are equivalent to IN and NOT IN, respectively, and so these two are important exceptions to the overall recommendation to avoid ALL and ANY comparisons in general—i.e., you can use =ANY and IN interchangeably, and you can use <>ALL and NOT IN interchangeably too. (Personally, I think IN and NOT IN are much clearer than their alternatives, but it’s your choice.) By contrast, =ALL and <>ANY have no analogous equivalents; however, expressions involving those operators can always be replaced by expressions involving EXISTS instead, as already noted.

Caveat: Unfortunately, the transformations involving MAX and MIN aren’t guaranteed to work if the MAX or MIN argument happens to be an empty set. The reason is that SQL defines the MAX and MIN of an empty set to be null. For example, here again is the formulation shown earlier for the query “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' )

And here’s a transformed “equivalent”:

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

Now suppose there are no blue parts. Then the first of the foregoing expressions will return all part names in table P, but the second will return an empty result.^[164]

Anyway, to make the transformation in the example valid after all, use COALESCE—e.g., as follows:

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  PX.WEIGHT > ( SELECT COALESCE ( MAX ( PY.WEIGHT ) , 0.0 )
                          FROM   P AS PY
                          WHERE  PY.COLOR = 'Blue' )

By way of another example, consider the query “Get part names for parts whose weight is less than that of some part in Paris.” Here’s a logical formulation:

     { PX.PNAME } WHERE EXISTS PY ( PY.CITY = 'Paris' AND
                                    PX.WEIGHT < PY.WEIGHT )

Here’s a corresponding SQL formulation:

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  EXISTS ( SELECT *
                     FROM   P AS PY
                     WHERE  PY.CITY = 'Paris'
                     AND    PX.WEIGHT < PY.WEIGHT )

But this query too could have been expressed in terms of an ALL or ANY comparison, thus:

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  PX.WEIGHT <ANY ( SELECT PY.WEIGHT
                             FROM   P AS PY
                             WHERE  PY.CITY = 'Paris' )

Result:

As this example suggests (and indeed as already stated), expressions involving ALL and ANY comparisons can always be transformed into equivalent expressions involving EXISTS instead. Some questions for you: