oracle忽略子查询中的无效标识符错误 [英] 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):

飞机( airc_model(PK)，airc_manufact)

AIRCRAFT (airc_model (PK), airc_manufact)

FLIGHT( flt_no(PK)， airc_model(FK))

如果我自己运行子查询，则由于airc_manufact不在排行表中的列，因此我将收到类似无效标识符"的错误.

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.

如果我运行整个查询，则不会收到错误消息. Oracle似乎忽略了子查询，因此给了我飞机表中的所有行.

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.

在我看来，这似乎是一个错误，因为查询中存在明显的错误.为什么查询运行?我的理解是，Oracle将首先运行或评估子查询，然后运行外部查询.

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.

推荐答案

您尚未限定列名.因此，您认为自己正在跑步:

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);

如果f.airc_manufact不存在，则作用域规则说要在外部查询中查找.因此，您真正正在运行的是:

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.

这篇关于oracle忽略子查询中的无效标识符错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！