带有损坏的子选择的查询应该导致错误但返回行

Query with broken sub-select should result in error but returns rows(带有损坏的子选择的查询应该导致错误但返回行)

问题描述

我不理解这种情况下的行为.据我了解，带有无效子查询的查询应该会导致错误.但在这个例子中，它返回了一些行.

I don't understand the behaviour in this case. In my understanding a query with an invalid sub-query should result in an error. But in this example it returns some rows.

测试数据:

create table test_values ( tst_id number, tst_id2 number, tst_value varchar2( 10 ) );

create table test_lookup ( tst_id number, tst_value varchar2( 10 ) );

insert into test_values( tst_id, tst_id2, tst_value ) values ( 1, 2, 'a' );
insert into test_values( tst_id, tst_id2, tst_value ) values ( 1, 2, 'b' );
insert into test_values( tst_id, tst_id2, tst_value ) values ( 2, 2,'c' );
insert into test_values( tst_id, tst_id2, tst_value ) values ( 2, 2,'d' );

insert into test_lookup( tst_id, tst_value ) values ( 1, 'findMe' );

commit;

按预期工作:

select * from test_values where tst_id in ( select tst_id from test_lookup where tst_value = 'findMe' );

/*
    TST_ID    TST_ID2 TST_VALUE 
---------- ---------- ----------
         1          2 b         
         1          2 a   
*/

select tst_id2 from test_lookup where tst_value = 'findMe'; 
--ORA-00904: "TST_ID2": invalid identifier

但以下查询也在检索行，显然是通过从test_values"表中获取test_id2"列，而不是从子查询中所述的test_lookup"表中获取，尽管没有使用别名内部和外部.

But the following query is also retrieving lines, obviously by taking the "test_id2"-column from the "test_values"-table and not from the "test_lookup"-table as stated in the sub-query and though NOT using aliases for inner and outer parts.

select * from test_values where tst_id in ( select tst_id2 from test_lookup where tst_value = 'findMe' );

/*
   TST_ID    TST_ID2 TST_VALUE  
---------- ---------- ----------
         2          2 c         
         2          2 d         
*/

推荐答案

原因是当子查询中不存在非别名列但外部查询中存在时，Oracle 假设您引用的列来自外部查询.

The reason is because when an unaliased column doesn't exist in the subquery but does exist in the outer query, Oracle assumes you are referring to the column from the outer query.

使用别名，您感到困惑的查询如下所示:

With aliases, the query you're confused about would look like:

select *
from   test_values tv
where  tv.tst_id in (select tv.tst_id2
                     from   test_lookup tl
                     where  tl.tst_value = 'findMe');

希望这能让事情更清楚吗?

Hopefully, that makes things clearer?

您看到的问题是一个很好的例子，说明了为什么您应该始终使用它们来自哪个表来标记您的列 - 这使得维护查询开始变得更加容易！

The issue you're seeing is a very good example of why you should always label your columns with which table they came from - it makes it much easier to maintain the query for a start!

这篇关于带有损坏的子选择的查询应该导致错误但返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！