Date:         Sun, 17 Dec 2006 22:42:23 -0500
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: SQL ANY/ALL Subquery Negation

On Sun, 17 Dec 2006 18:34:06 -0500, Sigurd Hermansen <HERMANS1@WESTAT.COM>
wrote:

>Howard:
>I suspect that since (item <relational op> item) appears within
parentheses, the SAS SQL compiler is following a SAS tradition and treating
the 'eq' as the op and 'name' and 'any' as items. I expect that the
conditions '(name IN (select ...))' and '(name NOT IN (select ...))' will
evaluate as logical complements. At first glance I see no advantage to the
'eq any' operator and at least one possible disadvantage. The set operator
IN doesn't need variations and qualifiers so far as I can see.
>Sig
>
>________________________________
>

The construct using "ANY" or "ALL" to compare a scalar with the result of a
subquery is documented:

"Sometimes it is helpful to compare a value with a set of values returned by
a subquery. The keywords ANY or ALL can be specified before a subquery when
the subquery is the right-hand operand of a comparison. If ALL is specified,
then the comparison is true only if it is true for all values that are
returned by the subquery. If a subquery returns no rows, then the result of
an ALL comparison is true for each row of the outer query."

"If ANY is specified, then the comparison is true if it is true for any one
of the values that are returned by the subquery. If a subquery returns no
rows, then the result of an ANY comparison is false for each row of the
outer query."

That's found on the "sql-expression" page in the Procedures Guide
(http://support.sas.com/onlinedoc/913/getDoc/en/proc.hlp/a002473695.htm)

I agree that when the check is for equality, why not use IN.

ANY and ALL can be used with other comparison operators.

I've reworked my example to use explicit parentheses in all three formulas,
and to use a non-equality comparisons:

   proc sql;

   create table one (name char);
   create table two (name char);
   insert into one(name)
    values ('Larry') values ('Moe') values ('Shemp');
   insert into two(name)
    values ('Larry') values ('Moe') values ('Curly');
   select  name
         ,     ( name LT any (select name from two) ) label = '(LT any)'
         , not ( name LT any (select name from two) ) label = 'not(LT any)'
         ,     ( name GE all (select name from two) ) label = '(GE all)'
    from one;

   quit;

Output:

                         not(LT
   name       LT any)      any)   GE all)
   --------------------------------------
   Larry            1         1         0
   Moe              0         1         1
   Shemp            0         1         1

I still do not see why the second boolean column is not, like the third, the
complement of the first.

>From: owner-sas-l@listserv.uga.edu on behalf of Howard Schreier <hs AT
dc-sug DOT org>
>Sent: Sun 12/17/2006 5:43 PM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: SQL ANY/ALL Subquery Negation
>
>
>
>I have a puzzling result.
>
>My code:
>
>  proc sql;
>
>   create table one (name char);
>   create table two (name char);
>   insert into one(name)
>    values ('Larry') values ('Moe') values ('Shemp');
>   insert into two(name)
>    values ('Larry') values ('Moe') values ('Curly');
>   select
>       name
>     ,       name  eq any (select name from two)   label = 'eq any'
>     , not ( name  eq any (select name from two) ) label = 'not(eq any)'
>     ,       name  ne all (select name from two)   label = 'ne all'
>    from one;
>
>   quit;
>
>The output:
>
>                         not(eq
>   name        eq any      any)    ne all
>   --------------------------------------
>   Larry            1         1         0
>   Moe              1         1         0
>   Shemp            0         1         1
>
>The expression for the middle boolean column is (a) the negation of the
>expression for the first boolean column and (b) seemingly logically
>equivalent to the expression for the last column.
>
>So, I expected the middle column to be the complement of the column to its
>left, and identical to the column to its right.