CODEWITHSUNDEEP
sqloracle-databasesubquery
Tina
Tina

Reputation: 11

oracle ignores invalid identifier error in subquery

I do not understand why the following query works, although the subquery gives an "invalid identifier" error.

SELECT *
FROM aircraft
WHERE airc_manufact IN (SELECT airc_manufact FROM flight);

My tables look the following (abbreviated):

AIRCRAFT (airc_model (PK), airc_manufact)

FLIGHT (flt_no (PK), airc_model (FK))

If I run the subquery on its own, then I receive an "invalid identifier" error like it should since the airc_manufact is not a column in the flight table.

If I run the whole query, then I do not receive an error. Oracle seems to ignore the subquery and thus give me all row in the aircraft table.

To me, this seems to be a bug because there is an obvious error in the query. Why does the query run? My understanding is that Oracle would first run or evaluate the subquery, and then run the outer query.

Upvotes: 1

Views: 249

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You have not qualified your column names. So, you think you are running:

SELECT a.*
FROM aircraft a
WHERE a.airc_manufact IN (SELECT f.airc_manufact FROM flight f);

If f.airc_manufact doesn't exist, then the scoping rules say to look in the outer query. So, what you are really running is:

SELECT a.*
FROM aircraft a
WHERE a.airc_manufact IN (SELECT a.airc_manufact FROM flight f);

That is pretty useless as a filtering clause.

Moral: Always qualify column names in a query, particularly if the query refers to more than one table.

Upvotes: 5

Related Questions