"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
FROMcustomer 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=1144width=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
thoughtfor 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
storehousenor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how
theygrow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not
arrayedlike one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast
intothe oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or
whatye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these
thingsshall be added unto you." Luke 12:22-24; 27-29; 31.
