Hi,

In a subquery, are =ALL and <>ALL always return false?  I understand =ALL but not <>ALL.  So please explain a little.

Thanks.

The predicate  "x = ALL (SELECT y FROM Foobar WHERE ...) is actually a
shorthand for this pseudo-code:
   (x = y[1]
     AND x = y[2]
     ... AND x = y[k] )

Likewise, the predicate  "x <> ALL (SELECT y FROM Foobar WHERE ...) is
actually a shorthand for this pseudo-code:

   (x <> y[1]
     AND x <> y[2]
     ... AND x <> y[k] )

Now assume that y[i] IS NULL for one or more rows so you have an UNNOWN
in the chain of ANDs:

TRUE AND UNKNOWN = UNKNOWN
FALSE AND UNKNOWN = FALSE

UNKNOWN and FALSE are treated as equal in the DML.

"ALL"  requires syntax like

  <Scalar constant or Sclar expression>
                <Operator> ALL
                         (Subquery returning Single Column)

It would be much clearer if it were the other way around...
(Subquery returning Single Column)
       <Operator> ALL
             <Scalar constant or Sclar expression>

i.e., EVERY value in the Subquery Must be <whatever the operator is> to the
Scalar..  nut given the first, proper syntax...
Examples

3 <> ALL (Subquery) -- There cannot be any 3s in the subquery
3  >  ALL (Subquery) -- All the values in subquery must be less than 3
etc...


Show quoteHide quote

ntext and update/insert triggers
Error converting data type nvarchar to int.
move records from table1 to table2 if does not exist in table3
Query Help
FIFO report