Oracle子查询的趣味性 [英] Oracle subquery funniness

问题描述

想象一个查询

SELECT Col_A FROM TABLE_A WHERE Col_A IN (SELECT Col_A FROM TABLE_B) 

除了TABLE_B没有Col_A列；只有TABLE_A拥有它.我在Oracle 12中进行了尝试，但不确定该版本的版本如何，但是看起来返回的是显示TABLE_A中所有Col_A数据的有效结果.作为开发人员，我期望这样的事情会引发错误，因为可以这么说，内部查询甚至不会再次访问TABLE_A.有人可以解释为什么或在哪里使用上述查询条件，因为我几乎觉得这应该是系统中的错误.

Except, TABLE_B does not have a Col_A column; Only TABLE_A has it. I tried this in Oracle 12 and I'm not sure how far back in versions it goes but it looks like is returns a valid result of showing all of Col_A data from TABLE_A. As a developer I'm expecting something like this to throw an error since the inner query does not even access the TABLE_A again so to speak. Can someone explain why or where we would use a query situation like the one above because I almost feel like it should be a bug in the system.

推荐答案

这不是错误.由于您没有对该列进行限定，因此Oracle会将其解释为表A中的当前列值，该值在范围之内(参见

It is not a bug. As you did not qualify the column, Oracle interprets it as the current column value from table A -- which is within scope (cf. a correlative sub query). So the subquery just produces as many duplicates of that column value as there are rows in table B.

基于此条件，in条件将变为true或false.当表B为空时，它将始终为false.在Col_A为null的情况下，它也将为false.但是在所有其他情况下，这都是正确的.因此，您将获得与表A中在Col_A中具有非空值的行一样多的结果，除非表B为空，在这种情况下您将没有结果.

The in condition will either become true or false based on that. It will always be false when table B is empty. It will also be false in cases where Col_A is null. But in all other cases it will be true. So you'll get as many results as there are rows in table A that have a non-null value in Col_A, except when table B is empty, in which case you get no results.

这篇关于Oracle子查询的趣味性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！