Hi

The following is the quote describing WHERE clause of SELECT
(pgsql/doc/html/sql-select.html).

"WHERE Clause 

The optional WHERE condition has the general form: 

WHERE boolean_expr
    

boolean_expr can consist of any expression which evaluates to a boolean value. In many cases,
this expression will be: 

     expr cond_op expr
    

or 

     log_op expr
    

where cond_op can be one of: =, <, <=, >, >= or <>, a conditional operator like ALL, ANY, IN,
                                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
LIKE, or a locally defined operator, and log_op can be one of: AND, OR, NOT. SELECT will ignore
all rows for which the WHERE condition does not return TRUE. "

Please give me hints how can I use "conditional operators ALL, ANY" in
WHERE clause.

Some examples will be appreciated.

Thanks in advance.

-- 
Vladimir Zolotych                         gsmith@eurocom.od.ua

Mon, 03 Sep 2001 11:30:32 +0300
"Vladimir V. Zolotych" <gsmith@eurocom.od.ua>
Conditional operators ALL, ANY in WHERE clause
"Vladimir V. Zolotych" wrote:
  >Please give me hints how can I use "conditional operators ALL, ANY" in
  >WHERE clause.
  
[This query would have been better directed to the pgsql-sql list.]

  >Some examples will be appreciated.

ALL is used to test a value against all of a list of items.

Find the customer whose account has been created the longest:

  SELECT id, date_opened
    FROM customer
    WHERE date_opened IS NOT NULL AND date_opened <= ALL (SELECT date_opened 
      FROM customer
      WHERE date_opened IS NOT NULL);

    id   | date_opened 
  -------+-------------
   25832 | 1998-01-05
  (1 row)


ANY is used to compare against any item of the list; "x = ANY y" is the
same as "x IN y":

  
  SELECT COUNT(*)
    FROM customer
    WHERE area = ANY (SELECT id
      FROM country);
   count 
  -------
     216
  (1 row)


But note that use of ALL may be very inefficient:

bray=# explain select id,date_opened from customer where date_opened is not 
null and date_opened <= all (select date_opened from customer where 
date_opened is not null);
NOTICE:  QUERY PLAN:

Seq Scan on customer  (cost=0.00..240125.47 rows=1144 width=16)
  SubPlan
    ->  Seq Scan on customer  (cost=0.00..139.89 rows=1144 width=4)

EXPLAIN
bray=# explain select id,date_opened from customer where date_opened is not 
null and date_opened <= (select min(date_opened) from customer);
NOTICE:  QUERY PLAN:

Seq Scan on customer  (cost=0.00..148.47 rows=381 width=16)
  InitPlan
    ->  Aggregate  (cost=139.89..139.89 rows=1 width=4)
          ->  Seq Scan on customer  (cost=0.00..131.31 rows=3431 width=4)

EXPLAIN
-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "And he said unto his disciples, Therefore I say unto 
      you, Take no thought for your life, what ye shall eat;
      neither for the body, what ye shall put on. For life 
      is more than meat, and the body is more than clothing.
      Consider the ravens, for they neither sow nor reap; 
      they have neither storehouse nor barn; and yet God  
      feeds them;  how much better you are than the birds!
      Consider the lilies, how they grow; they toil 
      not, they spin not; and yet I say unto you, that  
      Solomon in all his glory was not arrayed like one of 
      these. If then God so clothe the grass, which is to 
      day in the field, and tomorrow is cast into the oven;
      how much more will he clothe you, O ye of little  
      faith?  And seek not what ye shall eat, or what ye 
      shall drink, neither be ye of doubtful mind. 
      But rather seek ye the kingdom of God; and all these 
      things shall be added unto you."         
                              Luke 12:22-24; 27-29; 31. 



Mon, 03 Sep 2001 23:11:54 +0100
"Oliver Elphick" <olly@lfix.co.uk>
Re: Conditional operators ALL, ANY in WHERE clause 