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>