了解基本 SQL 查询
我有一个类似的查询
SELECT tran_number
FROM table_a WHERE customer_id IN
(SELECT customer_id
FROM table_b
WHERE customer_key = 89564
AND ( other_phn_area_code
|| other_phnum_pfx_num
|| other_phnum_sfx_num IN
(123456789)))
AND phn_area_code || phnum_pfx_num || phnum_sfx_num IN (123456789)
<小时>
上面的代码工作正常.问题在于内部查询(下面单独复制内部查询)...
The above code is working fine. The concern is with the inner query (copied inner query alone below)...
(SELECT customer_id
FROM table_b
WHERE customer_key = 89564
AND ( other_phn_area_code
|| other_phnum_pfx_num
|| other_phnum_sfx_num IN
(123456789)))
当我执行这个查询时，我收到错误 customer_id: invalid identifier.实际上，table_b 没有任何名为 customer_id 的字段.如果是这样，那么当我将它用作上面的内部查询时，它是如何工作的，没有任何问题.
When i execute this query, i'm getting error as customer_id: invalid identifier. In real, table_b is not having any field named customer_id. If so, then how it is working, without any issue, when i use it as an inner query above.
请帮助我理解这一点.
数据库详情如下
Oracle 11G Enterprise edition 11.2.0.2.0
PL/SQL Release 11.2.0.2.0
推荐答案
这是范围的问题.Oracle 从最里面的子查询开始验证标识符并向外工作.如果我们为您的原始查询添加表别名，事情可能会变得更清楚:
It's a matter of scope. Oracle validates identifiers starting with the innermost sub-query and working outwards. If we add table aliases to your original query things might become clearer:
SELECT t1.tran_number
FROM table_a t1
WHERE t1.customer_id IN
(SELECT t1.customer_id
FROM table_b t2
WHERE t2.customer_key = 89564
AND ( t2.other_phn_area_code
|| t2.other_phnum_pfx_num
|| t2.other_phnum_sfx_num IN
(123456789)))
AND t1.phn_area_code || t1.phnum_pfx_num || t1.phnum_sfx_num IN (123456789)
实际上，外部查询使用子查询作为 EXISTS 的测试，即仅检查给定值 CUSTOMER_KEY 和其他列是否存在.如果这不是您想要的，那么您应该更改子查询中的列名.(这是一个不错的选择:您可能会从主查询中得到令人费解的结果，这就是为什么您要单独调查子查询的原因).
In effect, the outer query is using the sub-querty as a test for EXISTS, i.e. just checking for the existence of a given value of CUSTOMER_KEY and those other columns. If this is not what you want then you should change the column name in the sub-query. (And that's a pretty good bet: you're probably getting puzzling results from the main query and that's why you're investigating the sub-query in isolation).
在这些场景中使用别名总是好的做法.如果您像这样为子查询起别名:
Using aliases in these scenarios is always good practice. If you had aliased the sub-query like this:
....
WHERE t1.customer_id IN
(SELECT t2.customer_id
FROM table_b t2
WHERE t2.customer_key = 89564
....
错误会立即显现出来.
SQL 参考确实解释了子查询中作用域的操作，但很难找到.它说是这样的:
The SQL Reference does explain the operation of scope in sub-queries, but it's hard to find. What it says is this:
"Oracle 通过查找来解析子查询中的不合格列在子查询中命名的表，然后在父声明"
"Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement"
您可以在 PL/SQL 文档中找到更清晰的范围说明；SQL 子查询以同样的方式工作.了解更多.
You can find a clearer explanation of scoping in the PL/SQL documentation; SQL sub-queries work in the same fashion. Find out more.
相关文章