深入left join的on条件和where条件的区别

你的名字 2022-06-16 06:48 206阅读 0赞

\#\#\#表结构  
看两个表tb\_order和tb\_user\_info的结构，tb\_order表的user\_id和tb\_user\_info表的id存在外键关系：

CREATE TABLE `tb_order` (
      `id` BIGINT NOT NULL AUTO_INCREMENT,
      `product_id` BIGINT NOT NULL DEFAULT 0 COMMENT '产品id',
      `product_name` varchar(64) NOT NULL DEFAULT '' COMMENT '产品名',
      `user_id` int NOT NULL DEFAULT '0' COMMENT '用户id',
      `spend` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '花费',
      `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0: 否, 1: 已经删除',
      INDEX `ix_user_id`(`user_id`),
      INDEX `ix_product_id`(product_id),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `tb_user_info` (
      `id` BIGINT NOT NULL AUTO_INCREMENT,
      `username` char(20) NOT NULL DEFAULT '',
      `sex` TINYINT NOT NULL DEFAULT 0 COMMENT '0: 中性, 1: 男, 2: 女',
      `age` INT NOT NULL DEFAULT 0,
      `is_delete` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除' COMMENT '0: 否, 1: 已经删除',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO tb_user_info(username, sex, age) VALUES ('Jack', 1, 24);
    INSERT INTO tb_user_info(username, sex, age) VALUES ('Tom', 1, 25);
    INSERT INTO tb_user_info(username, sex, age, is_delete) VALUES ('Rose', 2, 22, 1);
    INSERT INTO tb_user_info(username, sex, age, is_delete) VALUES ('Hanson', 0, 26, 1);
    
    INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(11, '潘多拉小饼', 1, 20);
    INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(12, '无问西东海报', 1, 25);
    INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(12, '星际穿越模型', 2, 25);
    INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(13, '头号玩家玩偶', 3, 25);
    INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(13, '头号玩家玩偶', 11, 25);
    INSERT INTO tb_order(product_id, product_name, user_id, spend) VALUES(13, '头号玩家玩偶', 12, 25);

\#\#\#sql语句

#### 1、join（inner join）查询 ####

结果如图：  
![这里写图片描述][70]

将上面的结果过滤掉user\_is\_delete为0的数据。  
注意`is_delete` 分别在on条件下和where条件下：

SELECT
      o.product_id,
      o.product_name,
      o.user_id   AS order_user_id,
      u.id        AS user_id,
      u.username,
      u.is_delete AS user_is_delete
    FROM tb_order o
      JOIN tb_user_info u ON o.user_id = u.id AND u.is_delete = 0;
    
    SELECT
      o.product_id,
      o.product_name,
      o.user_id   AS order_user_id,
      u.id        AS user_id,
      u.username,
      u.is_delete AS user_is_delete
    FROM tb_order o
      JOIN tb_user_info u ON o.user_id = u.id
    WHERE u.is_delete = 0;

上面两条sql语句执行的结果一样的：  
![这里写图片描述][70 1]

#### 2、left join 查询 ####

使用`left join`有一个奇妙的效果。

/* u.is_delete = 0 加在了on条件下 */
    /*sql1*/
    SELECT
      o.product_id,
      o.product_name,
      o.user_id   AS order_user_id,
      u.id        AS user_id,
      u.username,
      u.is_delete AS user_is_delete
    FROM tb_order o LEFT JOIN tb_user_info u ON o.user_id = u.id AND u.is_delete = 0

看结果:  
![这里写图片描述][70 2]  
`left join` 会将左边表的数据全给查询出来(即使和右边表没有mapping关系)，也正好就是有一个这样的需求：需要查询出全部的订单信息，有关联用户信息的就将用户信息查出来。

现在将`u.is_delete = 0` 写在where 条件里：

/*sql2*/
    SELECT
      o.product_id,
      o.product_name,
      o.user_id   AS order_user_id,
      u.id        AS user_id,
      u.username,
      u.is_delete AS user_is_delete
    FROM tb_order o LEFT JOIN tb_user_info u ON o.user_id = u.id
    WHERE u.is_delete = 0

![这里写图片描述][70 3]  
`ON o.user_id = u.id AND u.is_delete = 0`：join关联表的条件，`WHERE u.is_delete = 0`：where条件里会对join之后的数据(sql1查询的结果)进行过滤筛选。

如果在后台的一个页面需要展示tb\_order表的数据，显示订单的时候同时也要显示用户信息，已经被删除的用户数据不予显示。如果考虑使用`left join`去连接tb\_user\_info 这个表的数据，就得注意tb\_user\_info表的is\_delete字段应该放在什么位置(on后还是where后)。如果不小心放在where后了，就会少显示tb\_order表的数据(这是当时项目中经历过的坑)。

这种情况下使用join并不是明智的选择，因为tb\_order订单表的数据会很多，users表的数据相对会少很多，这样的两个表去做join操作，肯定影响性能。  
可以先查询出订单数据，然后通过订单数据里的user\_id list 使用in的方式去users表查询用户数据:

select * from tb_user_info where id in (1, 3, 4)

或直接将用户数据（比如user\_id、username）给冗余到tb\_order表中去，这也是大多数公司的做法。

参考链接：  
[MySQL LEFT JOIN][]  
[http://www.w3resource.com/sql/joins/perform-an-equi-join.php][http_www.w3resource.com_sql_joins_perform-an-equi-join.php]  
[https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/][https_blog.codinghorror.com_a-visual-explanation-of-sql-joins]

[70]: /images/20220616/7effc2e89a874d83acfc50ff1c352e93.png
[70 1]: /images/20220616/3a4b331096004111b8c84f445442f432.png
[70 2]: /images/20220616/001bcdb86f0c4d0caac995962fe2cebd.png
[70 3]: /images/20220616/a3c6912c1b4e4c53be20c149069148f0.png
[MySQL LEFT JOIN]: http://www.mysqltutorial.org/mysql-left-join.aspx
[http_www.w3resource.com_sql_joins_perform-an-equi-join.php]: http://www.w3resource.com/sql/joins/perform-an-equi-join.php
[https_blog.codinghorror.com_a-visual-explanation-of-sql-joins]: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/