Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/mwexler/public_html/tp/textpattern/lib/txplib_db.php on line 14
The Net Takeaway: ALL and ANY in SQL


Danny Flamberg's Blog
Danny has been marketing for a while, and his articles and work reflect great understanding of data driven marketing.

Eric Peterson the Demystifier
Eric gets metrics, analytics, interactive, and the real world. His advice is worth taking...

Geeking with Greg
Greg Linden created Amazon's recommendation system, so imagine what can write about...

Ned Batchelder's Blog
Ned just finds and writes interesting things. I don't know how he does it.

R at LoyaltyMatrix
Jim Porzak tells of his real-life use of R for marketing analysis.






ALL and ANY in SQL · 04/05/2004 02:26 PM, Database

There are some SQL commands which don’t get the daylight they deserve. Tom Kyte, of Ask Tom fame, always has good things to teach.

I enjoyed reading this entry which started off as a top-N query, and veered in all sorts of ntile and other directions.

Along the way, Tom pointed out that we often forget about the ANY/SOME and ALL options.

An example:

   select sal, deptno
   from emp
   where sal >= ANY ( select sal
                      from (select sal
                            from emp
                            order by sal desc)
                      where rownum <= 2 )
   order by deptno, sal

In this case, the query is looking for salaries which are greater than ANY of the items returned in the subquery. Why use the ANY? Because when you use >= with a subquery, it expects a SINGLE value, and a SINGLE row. If you don’t need a correlated subquery, this can be a clean way to code exactly the constraint you are looking for.

ALL works the same way, but means the condition must hold true for all of the returned values.

Yes, you could simulate this with a Max/Group by query in the sub, but this reads better in many ways.

* * *


  1. Select * from table1
    where cars =ALL ( ‘TOYOTA’, ‘BMW’);

    the above code does not work. Can you assist ?

    Yusuf    May 4, 05:02 AM    #

  Textile Help
Please note that your email will be obfuscated via entities, so its ok to put a real one if you feel like it...

powered by Textpattern 4.0.4 (r1956)