V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
git00ll
V2EX  ›  MySQL

数据库左连接和右连接是可以相互转换的吧,不太确定理解对不对

  •  
  •   git00ll · 2020-07-16 20:58:49 +08:00 · 2608 次点击
    这是一个创建于 1350 天前的主题,其中的信息可能已经有所发展或是发生改变。

    这两种写法是等价的吗

    select a.*, b.* from A a left join B b where a.bid = b.id
    
    select a.*, b.* from B b right join A a where a.bid = b.id
    
    
    第 1 条附言  ·  2020-07-17 11:38:59 +08:00

    感谢大家的回复!

    更正一下主题里面on笔误写成了where @hemingyang .

    昨晚翻看了《数据库原理与应用 - 基于SQL Server》 它里面是这样举例子的: 如果把两种表当作两个集合,那么

    • inner join就是两个集合的交集
    • left join 就是交集 + 左集合
    • right join 就是交集 + 右集合

    这样的话,参考stackoverflow上的讨论,

    left join 换成 right join,再将A B 表交换位置,查出的结果是等价的。

    17 条回复    2020-07-20 14:08:20 +08:00
    saulshao
        1
    saulshao  
       2020-07-16 21:10:32 +08:00
    这个是不是等价要取决于你的表设计,逻辑上是有差异的。
    就你的 SQL 而言,左连接我记得 a 表中存在,但是 b 表中不存在的 id,会被选出。右连接则相反。
    换句话说你可能会得到以下的一行数据:
    (a.id,a.name,null,null,null)其中的 null 值都来自 b 表中你假设的字段。

    我记得 MS SQL server books online 说得很清楚,关于这个概念。
    saulshao
        2
    saulshao  
       2020-07-16 21:12:39 +08:00
    如果你的 a,b 两个表中的 id 都是唯一索引,等价这个说法是成立的,但是假如 a 表中的 id 对应的行比 b 表多,我记得左连接结果会以 a 表中的行为准,反之也成立
    EminemW
        3
    EminemW  
       2020-07-16 21:49:04 +08:00
    *** a left join b ****会保留 a 表存在 但 b 表不存在的数据
    qwertyzzz
        4
    qwertyzzz  
       2020-07-16 22:40:33 +08:00
    新建 2 个表执行下?
    no1xsyzy
        5
    no1xsyzy  
       2020-07-17 02:10:54 +08:00
    @saulshao #2 慢点,你看下楼主的,在转换左右的同时 A B 位置也对调了……
    所以是一样的。
    no1xsyzy
        6
    no1xsyzy  
       2020-07-17 02:13:14 +08:00
    输出结果是一样的
    但中间的运行步骤是否一样就不清楚了。直觉上应该不会有不同。具体要 explain,微弱可能依赖于特定实现。
    leoleoasd
        8
    leoleoasd  
       2020-07-17 02:36:37 +08:00
    看上去, 除了语义, 没啥不同?
    leoleoasd
        9
    leoleoasd  
       2020-07-17 02:36:56 +08:00
    "There are no performance that can be gained if you'll rearrange LEFT JOINs to RIGHT."
    hemingyang
        10
    hemingyang  
       2020-07-17 08:57:04 +08:00
    不是 用 on 吗 为啥 你写 where
    liujavamail
        11
    liujavamail  
       2020-07-17 13:09:33 +08:00
    所以 left join 或者 right join 只是查询数据范围大小不一样,但加了 where 的条件之后, 最后都是一样的结果, 可以这么理解吗?

    就是如果 左表数据少,就用 left join, 右表数据少,就用 right join
    saulshao
        12
    saulshao  
       2020-07-17 15:50:23 +08:00
    我以前似乎是左表数据多采用 left join.....反之用 right join...
    但是假如你是真的要求交集,#11 的说法是正确的
    zhangysh1995
        13
    zhangysh1995  
       2020-07-19 17:55:47 +08:00
    @git00ll
    一句回答:A LEFT JOIN B 和 B RIGHT JOIN A 是等价的。LEFT JOIN 保留左表所有行,RIGHT JOIN 保留右边所有行。
    我自己刚写过一篇理解 JOIN 的文章,欢迎赏个脸 https://zhuanlan.zhihu.com/p/157249501

    @hemingyang
    WHERE 和 ON 在 JOIN 情况下没有什么区别。SQL Server 不太了解,在 MySQL 里面,ON 部分的条件也可以写 WHERE 里面,但是习惯是 ON 写 JOIN 条件,WHERE 写结果条件。文档如下:

    The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.
    JasonLaw
        14
    JasonLaw  
       2020-07-19 22:40:24 +08:00
    @zhangysh1995 #13

    你说“WHERE 和 ON 在 JOIN 情况下没有什么区别”,你是说 inner join 吗?如果是的话,你说得没错,但是我想更加明确地说出,where 和 on 在 outer join 的表现是有区别的。

    在“Database System Concepts 6th edition - Chapter 4 Intermediate SQL - 4.1 Join Expressions - 4.1.1 Join Conditions”中,说了“However, there are two good reasons for introducing the on condition. First, we shall see shortly that for a kind of join called an outer join, on conditions do behave in a manner different from where conditions. Second, an SQL query is often more readable by humans if the join condition is specified in the on clause and the rest of the conditions appear in the where clause.”。之后在“4.1.2 Outer Joins”中,它说“As we noted earlier, on and where behave differently for outer join. The reason for this is that outer join adds null-padded tuples only for those tuples that do not contribute to the result of the corresponding inner join. The on condition is part of the outer join specification, but a where clause is not.”。

    我这里用例子演示一下 on 和 where 的区别吧。
    create table t1(c1 int);
    create table t2(c2 int);
    insert into t1 values(1),(2),(3);
    insert into t2 values(1);

    select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; 的结果为
    +------+------+
    | c1 | c2 |
    +------+------+
    | 1 | 1 |
    | 2 | NULL |
    | 3 | NULL |
    +------+------+

    select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; 的结果为
    +------+------+
    | c1 | c2 |
    +------+------+
    | 1 | 1 |
    +------+------+
    JasonLaw
        15
    JasonLaw  
       2020-07-19 22:51:55 +08:00
    @no1xsyzy #6
    @leoleoasd #9

    虽然说 a left join b on a.bid = b.id 和 b right join a on a.bid = b.id 得到的结果是一样的,但是因为它们的 a 和 b 的顺序还是会造成处理的性能差异的,具体可以看看“Database System Concepts 6th edition - Chapter 12 Query Processing - 12.5 Join Operation”,因为内容是在太多了,在评论里讲不清楚。
    zhangysh1995
        16
    zhangysh1995  
       2020-07-20 13:36:20 +08:00
    @JasonLaw 我没有说 select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; 和 select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; 是一样的。很明显你给的两条并不是等价的。我指的是同一个条件放在 on 或者放在 where 是一样的,请不要曲解意思。

    这两条是等价的。
    select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2;
    select t1.*, t2.* from t1 left join t2 where t1.c1 = t2.c2;
    JasonLaw
        17
    JasonLaw  
       2020-07-20 14:08:20 +08:00
    @zhangysh1995 你可以自己去执行一下,看看是不是等价的,如果是的话,麻烦分享一下语句,让我重现一下。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5506 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 07:58 · PVG 15:58 · LAX 00:58 · JFK 03:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.