左联接中的歧义(仅适用于Oracle?) [英] Ambiguity in Left joins (oracle only?)

问题描述

我的老板在我创建的查询中发现了一个错误，尽管查询结果证明他是正确的，但我不理解该错误的原因.这是修复之前的查询(简化版):

My boss found a bug in a query I created, and I don't understand the reasoning behind the bug, although the query results prove he's correct. Here's the query (simplified version) before the fix:

select PTNO,PTNM,CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 

这里是修复之后:

select PTNO,PTNM,PARTS.CATCD
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD); 

错误是，CATCD列显示了空值，即查询结果包括表CATEGORIES的结果而不是PARTS的结果. 这是我不明白的:如果原始查询中存在歧义，为什么Oracle不会抛出错误?据我了解，在左联接的情况下，查询(PARTS)中的主"表具有歧义性. 我是错了，还是只是没有正确考虑这个问题?

The bug was, that null values were being shown for column CATCD, i.e. the query results included results from table CATEGORIES instead of PARTS. Here's what I don't understand: if there was ambiguity in the original query, why didn't Oracle throw an error? As far as I understood, in the case of left joins, the "main" table in the query (PARTS) has precedence in ambiguity. Am I wrong, or just not thinking about this problem correctly?

更新:

这是一个修改后的示例，其中不会引发歧义错误:

Here's a revised example, where the ambiguity error is not thrown:

CREATE TABLE PARTS (PTNO NUMBER, CATCD NUMBER, SECCD NUMBER);

CREATE TABLE CATEGORIES(CATCD NUMBER);

CREATE TABLE SECTIONS(SECCD NUMBER, CATCD NUMBER);


select PTNO,CATCD 
from PARTS 
left join CATEGORIES on (CATEGORIES.CATCD=PARTS.CATCD) 
left join SECTIONS on (SECTIONS.SECCD=PARTS.SECCD) ;

有人知道吗?

推荐答案

恐怕我不能告诉你为什么你没有例外，但是我可以推测一下为什么它选择了CATEGORIES版本的有关PARTS版本的专栏.

I'm afraid I can't tell you why you're not getting an exception, but I can postulate as to why it chose CATEGORIES' version of the column over PARTS' version.

据我了解，在左联接的情况下，查询(PARTS)中的主"表在歧义上优先

As far as I understood, in the case of left joins, the "main" table in the query (PARTS) has precedence in ambiguity

目前尚不清楚，从概念上看查询时，"main"是指左连接中的左表还是"driving"表.但是无论哪种情况，您都将"main"视为您编写的查询中的"表不一定是该查询实际执行中的主"表.

It's not clear whether by "main" you mean simply the left table in a left join, or the "driving" table, as you see the query conceptually... But in either case, what you see as the "main" table in the query as you've written it will not necessarily be the "main" table in the actual execution of that query.

我的猜测是，Oracle在执行查询时只是使用它命中的第一个表中的列.而且由于SQL中的大多数单独操作不需要先击打一个表，因此DBMS将在解析时决定哪个是最有效的扫描对象.尝试获取查询的执行计划.我怀疑它可能表明它先击中类别，然后击中零件.

My guess is that Oracle is simply using the column from the first table it hits in executing the query. And since most individual operations in SQL do not require one table to be hit before the other, the DBMS will decide at parse time which is the most efficient one to scan first. Try getting an execution plan for the query. I suspect it may reveal that it's hitting CATEGORIES first and then PARTS.

这篇关于左联接中的歧义(仅适用于Oracle?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！