Thursday, September 20, 2012

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>



According to the book advertised above, my 2nd example uses a correlated subquery. You can see what I mean in the excerpt below, which I copied from http://books.google.com. As usual, click on the image to enlarge it and bring it into focus:


No comments: