Tuesday, September 25, 2012

Subqueries with ANY or ALL

This was tested on Oracle 11.2. You can use ALL before a subquery as follows:
 
SQL> l
  1  select count(*)
  2  from dba_segments
  3  where segment_type = 'TABLE'
  4  and bytes > all
  5  (select bytes from dba_segments
  6*  where segment_type = 'INDEX')
SQL> /
 
  COUNT(*)
----------
         2
 
SQL>
 
This is fairly intuitive. It counts the number of tables which are bigger than all indexes in the database. The SQL below does the same thing:
 
SQL> l
  1  select count(*)
  2  from dba_segments
  3  where segment_type = 'TABLE'
  4  and bytes >
  5  (select max(bytes) from dba_segments
  6*  where segment_type = 'INDEX')
SQL> /
 
  COUNT(*)
----------
         2
 
SQL>
 
You can also use ANY before a subquery like this:
 
SQL> l
  1  select count(*)
  2  from dba_segments
  3  where segment_type = 'TABLE'
  4  and bytes > any
  5  (select bytes from dba_segments
  6*  where segment_type = 'INDEX')
SQL> /
 
  COUNT(*)
----------
       754
 
SQL>
 
The effect of this is not quite so obvious. It counts the number of tables which are bigger than at least one of the indexes in the database. The following SQL is equivalent:
 
SQL> l
  1  select count(*)
  2  from dba_segments
  3  where segment_type = 'TABLE'
  4  and bytes >
  5  (select min(bytes) from dba_segments
  6*  where segment_type = 'INDEX')
SQL> /
 
  COUNT(*)
----------
       754
 
SQL>

No comments: