Thursday, March 13, 2014

Can You Use BETWEEN and a Subquery Together?

I saw a colleague about to throw away his copy of Oracle 7 The Complete Reference so I rescued it from the bin and started to read it (as you do). It said that you could not use BETWEEN and a subquery in the same SQL statement and I wondered if this restriction still applied. The oldest version of Oracle I have access to is Oracle 9 so I decided to try it out there. The examples I came up with are a bit artificial but they show that you can now use BETWEEN and a subquery in the same SQL statement. Furthermore, the subquery can be:
 
(1)  Before the AND.
(2)  After the AND.
(3)  Before and after the AND.   
 
SQL> select count(*) from dba_users
  2  where created between
  3  (select created from dba_users
  4   where username = 'DATALOAD')
  5  and '31-DEC-2013'
  6  /
 
  COUNT(*)
----------
        73
 
SQL> select count(*) from dba_objects
  2  where object_id between
  3  100 and
  4  (select max(object_id) from dba_objects
  5   where owner = 'SYSTEM')
  6  /
 
  COUNT(*)
----------
      8850
 
SQL> select count(*) from dba_tables
  2  where initial_extent between
  3  (select min(initial_extent)
  4   from dba_tables
  5   where owner = 'SYSTEM')
  6  and
  7  (select max(initial_extent)
  8   from dba_tables
  9   where owner = 'SYSTEM')
10  /
 
  COUNT(*)
----------
      1196
 
SQL>

No comments: