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>