gpt4 book ai didi

mysql - 表连接顺序会影响查询结果吗?

转载 作者:行者123 更新时间:2023-11-28 23:36:05 24 4
gpt4 key购买 nike

一共有三张表t1,t2,t3,每张表都有一个字段f。这些对的 sql 是否相同?

1.select t1.f,t2.f,t3.f from t1 left join t2 on t1.f =t2.f left join t3 on t1.f = t3.f

select t1.f,t2.f,t3.f from t1 left join t3 on t1.f =t3.f left join t2 on t1.f = t2.f

2.select t1.f,t2.f,t3.f from t1 left join t2 on t1.f =t2.f inner join t3 on t1.f = t3.f

select t1.f,t2.f,t3.f from t1 inner join t3 on t1.f =t3.f left join t2 on t1.f = t2.f

3.select t1.f,t2.f,t3.f from t1 left join t2 on t1.f =t2.f right join t3 on t1.f = t3.f

select t1.f,t2.f,t3.f from t1 right join t3 on t1.f =t3.f left join t2 on t1.f = t2.f

4.select t1.f,t2.f,t3.f from t1 left join t2 on t1.f =t2.f full join t3 on t1.f = t3.f

select t1.f,t2.f,t3.f from t1 full join t3 on t1.f =t3.f left join t2 on t1.f = t2.f

5.select t1.f,t2.f,t3.f from t1 left join t2 on t1.f =t2.f left join t3 on t2.f = t3.f

select t1.f,t2.f,t3.f from t1 left join (t2 left join t3 on t2.f = t3.f) on t1.f =t2.f

...保持三张表中的任意两张表具有相同的连接和相同的连接字段。查询结果是否相同?如何简单理解?

我想知道如何证明它。我用oracle(+)连接表时,发现(+)不显示表连接顺序。如果结果不同,oracle默认顺序是什么?

我知道 t1 left join t2 不同于 t2 left join t1,因为它转换为 (+) 是不同的 sql。上面的sql,你转成(+)也是一样的。

例如,最后一对 sql 可以转换为从 t1、t2、t3 中选择 t1.f、t2.f、t3.f,其中 t1(+)=t2 和 t2(+)=t3,如 where clause no order.And in sql join's wiki,显式连接和隐式连接是平等的。因此,如果成对的 sql 的结果不同,那就是矛盾。

最佳答案

这是我最好的知识:

A左加入B~A∪(A∩B)~一个

A右加入B~(A∩B)∪B~B

A完全加入B~ A ∪ B

A 内连接 B~A∩B

#1
t1 ∪ (t1 ∩ t3) t1 ∪ (t1 ∩ t2)
~ t1 ~ t1

#2
t1 ∩ t3 (t1 ∩ t3) ∪ (t1 ∩ t3 ∩ t2)
~ t1 ∩ t3 ~ t1 ∩ t3

#3
(t1 ∩ t3 ) ∪ t3 t3 ∪ (t1 ∩ t3 ∩ t2)
~ t3 ~ t3

#4
t1 ∪ t3 (t1 ∪ t3) ∪ (t1 ∪ (t1 ∩ t2))
~ t1 ∪ t3 ~ t1 ∪ t3

#5
(t1 ∪ (t1 ∩ t2)) ∪ (t1 ∩ t2 ∩ t3) t1 ∪ (t1 ∩ (t2 ∪ (t2 ∩ t3)))
~ t1 ∪ (t1 ∩ t2 ∩ t3) ~ t1 ∪ (t1 ∩ t2)
~ t1 ~ t1

即使是第一个表达式,我也试图让它更短以避免混淆。对于#5,我把它写得更长了一点,以便更清楚。如果您想查看完整的扩展表达式,请告诉我。

关于mysql - 表连接顺序会影响查询结果吗?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/35704309/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com