问题描述
我不明白为什么以下查询有效，尽管子查询给出了无效标识符"错误.
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)
AIRCRAFT (airc_model (PK), airc_manufact)
飞行(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 忽略子查询中的无效标识符错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！