子选择损坏的查询应导致错误，但返回行 [英] 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!

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