Oracle SQL – Why Does This Query Work?

oracleselectsubquery

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?

Best Answer

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.

Related Solutions

Why Sqlcode 100 does not come with exception

I will explain the sequence of actions.

The select returns no rows and throws an exception NO_DATA_FOUND
The WHEN OTHERS exception handler handles the exception.
the Raise_Application_Error should raise the new exception.

Problems:

The use of the sqlcode variable is not wise, you should remove it as it is not within an error handler.
The EXIT statements after the RAISE_APPLICATION_ERROR's are dead code.
I cannot quite see how you could see the ora-00100 error with this code and still be correct in what you wrote.

Have to select from the dual table

Strictly, yes, the FROM clause of a SELECT statement is not optional. The syntax for SQL-99 details the basic SELECT statment, and the FROM clause doesn't have any square brackets around it. That indicates the standard considers it non-optional:

SELECT [ DISTINCT | ALL ]
{Column expression [ AS name ]} [ ,... ] | *
FROM <Table reference> [ {,<Table reference>} ... ]
[ WHERE search condition ]
[ GROUP BY Columns [ HAVING condition ] ]
[ORDER BY {col_name | expr | position} [ASC | DESC],...]                                     
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options |
 INTO DUMPFILE 'file_name' |
 INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]

In actual use, programmers and DBAs often find it useful to do things other than manipulate data in tables or manipulate tables and data structures. This type of thing is largely beyond the scope of the SQL standard, which is concerned with the data features more than the nuts and bolts of specific implementations. Whether we want to run SELECT getdate() or SELECT 1 or SELECT DB_NAME() (or whatever your dialect prefers), we don't actually want data from a table.

Oracle chooses to solve the standard and implementation discrepancy using a dummy table with the following effective definition:

CREATE TABLE DUAL (
  DUMMY CHAR(1)
  )

INSERT INTO DUAL (DUMMY) VALUES ('X')

Other RDBMSes essentially assume that a dummy table is used if no FROM is specified.

The history of the DUAL table is on Wikipedia:

The DUAL table was created by Charles Weiss of Oracle corporation to provide a table for joining in internal views:

I created the DUAL table as an underlying object in the Oracle Data Dictionary. It was never meant to be seen itself, but instead used inside a view that was expected to be queried. The idea was that you could do a JOIN to the DUAL table and create two rows in the result for every one row in your table. Then, by using GROUP BY, the resulting join could be summarized to show the amount of storage for the DATA extent and for the INDEX extent(s). The name, DUAL, seemed apt for the process of creating a pair of rows from just one.

The original DUAL table had two rows in it (hence its name), but subsequently it only had one row.

Best Answer

Related Solutions

Why Sqlcode 100 does not come with exception

Have to select from the dual table

Related Question