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>

SQL MINUS Operator

If you join two SELECT statements together with a MINUS, the output from the second SELECT is removed from the output from the first SELECT before the results are displayed. As always, it is easier to explain by using an example. First create a table for odd and even numbers and another table just for odd numbers:

SQL> create table odd_and_even
  2  (col1 number)
  3  /

Table created.

SQL> create table odd
  2  (col1 number)
  3  /

Table created.

SQL>

Now add some odd and even numbers to the first table then insert the contents again so that each number is stored twice (you will see why later):

SQL> insert into odd_and_even values (1);

1 row created.

SQL> insert into odd_and_even values (2);

1 row created.

SQL> insert into odd_and_even values (3);

1 row created.

SQL> insert into odd_and_even values (4);

1 row created.

SQL> insert into odd_and_even select * from odd_and_even;

4 rows created.

SQL> select * from odd_and_even

  2  /

      COL1

----------

         1

         2

         3

         4

         1

         2

         3

         4

8 rows selected.

SQL>

Now insert some odd numbers into the second table but do not replicate the values:

SQL> insert into odd values (1);

1 row created.

SQL> insert into odd values (3);

1 row created.

SQL> select * from odd

  2  /

      COL1

----------

         1

         3

SQL>

Now display the contents of the first table but before displaying the results, use the MINUS statement to remove any values which appear in the second table:

SQL> select col1 from odd_and_even

  2  minus

  3  select col1 from odd

  4  /

      COL1

----------

         2

         4

SQL>
  

Note that there were four odd numbers in the first table i.e. 1, 3, 1, 3 but only two odd numbers in the second table i.e. 1, 3. Even so, all the odd numbers have been subtracted from the output. This is equivalent to either of the following two SQL statements: 

SQL> select distinct col1 from odd_and_even

  2  where col1 not in

  3  (select col1 from odd)

  4  /

      COL1

----------

         2

         4

SQL> select distinct col1 from odd_and_even x

  2  where not exists

  3  (select col1 from odd

  4   where col1 = x.col1)

  5  /

      COL1

----------

         2

         4

SQL>

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>

Reply to: Reply to Andrew Reid

Send

How to Compare More Than 1 Column From a Subquery (revisited)

In my earlier post with the same name, the main query and the subquery looked at the same number of columns. The examples below were tested on Oracle 11.2. If the main query has more columns than the subquery, you get an ORA-00947:

 

SQL> l

  1  select count(*) from dba_tables

  2  where (owner, table_name, tablespace_name) not in

  3* (select table_owner, table_name from dba_indexes)

SQL> /

(select table_owner, table_name from dba_indexes)

*

ERROR at line 3:

ORA-00947: not enough values

 

SQL>

 

... and if the subquery has more values, you get an ORA-00913:  

 

SQL> l

  1  select count(*) from dba_tables

  2  where (owner, table_name) not in

  3  (select table_owner, table_name, table_type

  4*  from dba_indexes)

SQL> /

(select table_owner, table_name, table_type

*

ERROR at line 3:

ORA-00913: too many values

 

SQL>

How to Compare More Than 1 Column From a Subquery

This was tested on Oracle 11.2. You can compare more than 1 column from a subquery as follows:

SQL> select count(*)

  2  from dba_tables

  3  where (owner, table_name) not in

  4  (select table_owner, table_name

  5   from dba_indexes);

  COUNT(*)

----------

       601

SQL>

If you don’t like that, this does the same thing:

SQL> l

  1  select count(*)

  2  from dba_tables x

  3  where not exists

  4  (select table_owner, table_name

  5   from dba_indexes

  6   where table_owner = x.owner

  7*  and   table_name  = x.table_name)

SQL> /

  COUNT(*)

----------

       601

SQL>

Subquery with Order By?

This was tested on Oracle 11.1.0.6.0 running on Windows XP. Looking through some course notes from 1990 (as you do), I read that you cannot include an order by in a subquery. I'm not sure why you would ever want to do such a thing but I decided to see what happened if you did:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by 1)
SQL> /
 order by 1)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5   from dba_indexes
  6*  order by table_name)
SQL> /
 order by table_name)
 *
ERROR at line 6:
ORA-00907: missing right parenthesis

SQL>

It gave me an ORA-00907, as you can see above, which was not especially helpful. I removed the order by and the query ran successfully:

SQL> l
  1  select count(*)
  2  from dba_tables
  3  where table_name not in
  4  (select table_name
  5*  from dba_indexes)
SQL> /

  COUNT(*)
----------
       444

SQL>

However, I have just thought of a special kind of subquery, sometimes called an in-line view, which is allowed to have an order by. Here is an example, suggested by Laurent in the 1st comment below:

SQL> l

  1  select * from

  2  (select sid, a.value/100 CPU_Seconds

  3   from v$sesstat a, v$sysstat b

  4   where a.statistic# = b.statistic#

  5   and name = 'CPU used by this session'

  6   order by a.value desc)

  7* where rownum < 6

SQL> /

       SID CPU_SECONDS

---------- -----------

        71      401.64

       140      361.04

        10      306.59

         9         306

        43      280.75

SQL>

ORA-00937

This was tested on Oracle 11.2. You might think that, if you wanted to see details from a row containing some maximum value, you could do it as follows:

 

SQL> select owner, segment_name, max(bytes)

  2  from dba_segments

  3  /

select owner, segment_name, max(bytes)

       *

ERROR at line 1:

ORA-00937: not a single-group group function

 

SQL>

 

... but you get an ORA-00937. You have to do it like this:

 

SQL> l

  1  select owner, segment_name, bytes

  2  from dba_segments

  3  where bytes =

  4* (select max(bytes) from dba_segments)

SQL> /

 

OWNER           SEGMENT_NAME         BYTES

--------------- --------------- ----------

SYS             IDL_UB1$         251658240

 

SQL>

Reply to: Reply to Andrew Reid

Send

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:

 

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> create role role_a

  2  /

 

Role created.

 

SQL> create role role_b

  2  /

 

Role created.

 

SQL> grant role_a to user_1

  2  identified by user_1

  3  /

 

Grant succeeded.

 

SQL> grant role_a, role_b to user_2

  2  identified by user_2

  3  /

 

Grant succeeded.

 

SQL> grant role_b to user_3

  2  identified by user_3

  3  /

 

Grant succeeded.

 

SQL> select distinct grantee from dba_role_privs

  2  where grantee in

  3  (select grantee from dba_role_privs

  4   where granted_role = 'ROLE_A')

  5  and grantee in

  6  (select grantee from dba_role_privs

  7   where granted_role = 'ROLE_B')

  8  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'ROLE_A'

  3  intersect

  4  select grantee from dba_role_privs

  5  where granted_role = 'ROLE_B'

  6  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'BLAH'

  3  /

 

GRANTEE

------------------------------

ORACLE

 

SQL>