左连接是可交换的吗?它有什么特性? [英] Is left join commutative? What are its properties?

问题描述

假设表TableA TableB TableC和TableD:

是以下查询:

TableA INNER JOIN TableB LEFT JOIN TableC LEFT JOIN TableD

(都加入到id列中)等效于:

(all joined to an id column) equivalent to:

TableA INNER JOIN TableB
     INNER JOIN TableC
     LEFT JOIN TableD    

UNION  

TableA INNER JOIN TableB
    LEFT JOIN TableC ON TableB.c_id IS NULL
    LEFT JOIN TableD    

?

注意:
或代替工会

Note:
Or instead of union just do

TableA INNER JOIN TableB
       INNER JOIN TableC
        LEFT JOIN TableD  

然后

TableA INNER JOIN TableB
       LEFT JOIN TableC ON TableB.c_id IS NULL
       LEFT JOIN TableD    

然后合并结果

更新
是

(A INNER JOIN B) LEFT JOIN C LEFT JOIN D 

与:

A INNER JOIN (B LEFT JOIN C) LEFT JOIN D

?

推荐答案

维基百科:

在数学中，如果改变操作数的顺序不改变结果，则二进制运算是可交换的.它是许多二进制运算的基本属性，许多数学证明都依赖于此."

"In mathematics, a binary operation is commutative if changing the order of the operands does not change the result. It is a fundamental property of many binary operations, and many mathematical proofs depend on it."

答案:

不，左连接不是可交换的.内部联接是.

no, a left join is not commutative. And inner join is.

但这并不是您真正要的.

But that's not really what you are asking.

是以下查询:

TableA INNER JOIN TableB LEFT JOIN TableC LEFT JOIN TableD

(均已加入到id列)等同于:

(all joined to an id column) equivalent to:

TableA INNER JOIN TableB
       INNER JOIN TableC
        LEFT JOIN TableD   
UNION     
TableA INNER JOIN TableB
        LEFT JOIN TableC ON TableB.c_id IS NULL
        LEFT JOIN TableD    

答案:

也没有.一般来说，联合和联接实际上并不能完成相同的任务.在某些情况下，您可能可以等效地编写它们，但是我认为您显示的不是一般的伪sql. ON构造似乎不应该起作用(也许是我在MySQL中不知道的某些事情?)

Also no. Unions and joins don't really accomplish the same thing, generally speaking. In some case you may be able to write them equivalently, but I don't think so general pseudo sql you are showing. The ON constitution seemslike it should not work (maybe something about which I do not know in MySQL?)

这是一组简化的查询，我认为这些查询是等效的.

Here is a simplified set of queries that I do think would be equivalent.

SELECT * 
  FROM TableA a 
       LEFT JOIN 
       TableB b ON a.id = b.id_a 

SELECT * 
  FROM TableA a 
       INNER JOIN 
       TableB b ON a.id = b.id_a 
UNION      
SELECT * 
  FROM TableA a  
       LEFT JOIN 
       TableB b ON a.id = b.id_a 
 WHERE TableB.id IS NULL

这是距离您更近但本质上相同的另一个例子.

Here's another example that is closer to your but in essence the same.

SELECT * 
  FROM            TableA a 
       INNER JOIN TableB b ON a.id = b.id_a 
        LEFT JOIN TableC c ON b.id = c.id_b 

与

SELECT * 
  FROM TableA a 
       INNER JOIN TableB b ON a.id = b.id_a 
       INNER JOIN TableC c ON b.id = c.id_b 
UNION      
SELECT * 
  FROM TableA a  
       INNER JOIN TableB b ON a.id = b.id_a 
        LEFT JOIN TableC c ON b.id = c.id_b 
 WHERE TableC.id IS NULL

但是我仍然不认为我在回答您的真实问题.

But I still don't think I'm answering your real question.

这篇关于左连接是可交换的吗?它有什么特性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！