37

I have two tables, table_a (id, name) and table_b (id), let's say on Oracle 12c.

Why does this query not return an exception?

select * from table_a where name in (select name from table_b);

From what I understand, Oracle sees this as

select * from table_a where name = name;

But what I don't get is why?

61

The query is syntactically correct SQL even if table_b does not have a name column. The reason is scope resolution.

When the query is parsed, it is first checked whether table_b has a name column. Since it doesn't, then table_a is checked. It would throw an error only if neither of the tables had a name column.

Finally the query is executed as:

select a.* 
from table_a  a
where a.name in (select a.name 
                 from table_b  b
                );

As for the results the query would give, for every row of table_a, the subquery (select name from table_b) - or (select a.name from table_b b) - is a table with a single column with the same a.name value and as many rows as table_b. So, if table_b has 1 or more rows, the query runs as:

select a.* 
from table_a  a
where a.name in (a.name, a.name, ..., a.name) ;

or:

select a.* 
from table_a  a
where a.name = a.name ;

or:

select a.* 
from table_a  a
where a.name is not null ;

If table_b is empty, the query will return no rows (thnx to @ughai for pointing that possibility).


That (the fact that you don't get an error) is probably the best reason that all column references should be prefixed with the table name/alias. If the query was:

select a.* from table_a where a.name in (select b.name from table_b); 

you would have got the error straight away. When table prefixes are omitted, it is not difficult for such mistakes to happen, especially in more complex queries, and even more important, go unnoticed.

Read also in Oracle docs: Resolution of Names in Static SQL Statements the similar example B-6 in Inner capture and the recommendations in the Avoiding Inner Capture in SELECT and DML Statements paragraphs:

Qualify each column reference in the statement with the appropriate table alias.

  • How did you dissect the inner workings of the SQL engine so accurately? – RinkyPinku Feb 16 at 14:54
8

Because

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement one level above the subquery. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357

It means in order to determine whether subquery is correlated Oracle must try to resolve names in subquery including outer statement context too. And for unprefixed name it's the only resolution possible.

4

There is no name field in table_b so Oracle takes the one from table_a. I tried the EXPLAIN PLAN but this gave me only that there is a TABLE ACCESS FULL. I presume that this will generate some kind of Cartesian Product between both tables that result in a list of all the names in table_a is returned by the sub-query.

  • 5
    "There is no name field in table_b so Oracle takes the one from table_a." Correct. "I presume that this will generate some kind of Cartesian Product." Wrong. The query has from table_a where .... It will return all rows from table_a except those that name is null. – ypercubeᵀᴹ Jun 27 '16 at 9:04
  • 1
    TABLE ACCESS FULL is just Oracle's way of telling you it's doing a sequential scan. – Joishi Bodio Jun 27 '16 at 15:51
  • 1
    Your PLAN is irrelevant - there may well be indexing with huge tables - I'm assuming that your running on test data? – Vérace Jun 27 '16 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.