了解基本的 SQL 查询 [英] Understanding a basic SQL query
问题描述
我有一个类似
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 Reference 确实解释了子查询中作用域的操作,但是很难找到.它说是这样的:
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.
这篇关于了解基本的 SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!