问题描述

我有一个类似的查询

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.

这篇关于了解基本 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！