The query is syntactically correct SQL even iftable_b does not have aname column. The reason is scope resolution.

When the query is parsed, it is first checked whethertable_b has aname column. Since it doesn't, thentable_a is checked. It would throw an error only if neither of the tables had aname column.

Finally the query is executed as:

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

As for the results the query would give, for every row oftable_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 samea.name value and as many rows astable_b. So, iftable_b has 1 or more rows, the query runs as:

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

or:

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

or:

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

Iftable_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 inOracle docs: Resolution of Names in Static SQL Statements the similar example B-6 inInner capture and the recommendations in theAvoiding 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

  – RinkyPinku

  2019-02-16 14:54:16 +00:00

  CommentedFeb 16, 2019 at 14:54

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 Oraclemust try to resolve names in subquery including outer statement context too. And for unprefixedname it's the only resolution possible.

There is noname field intable_b so Oracle takes the one fromtable_a. I tried theEXPLAIN PLAN but this gave me only that there is aTABLE ACCESSFULL. I presume that this will generate some kind of Cartesian Product between both tables that result in a list of all the names intable_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 hasfrom table_a where .... It will return all rows fromtable_a except those thatname is null.

  ypercubeᵀᴹ

  – ypercubeᵀᴹ

  2016-06-27 09:04:16 +00:00

  CommentedJun 27, 2016 at 9:04
• 1
  TABLE ACCESS FULL is just Oracle's way of telling you it's doing a sequential scan.

  Joishi Bodio

  – Joishi Bodio

  2016-06-27 15:51:19 +00:00

  CommentedJun 27, 2016 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

  – Vérace

  2016-06-27 18:02:40 +00:00

  CommentedJun 27, 2016 at 18:02

• oracle
• select
• subquery