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

###表结构
看两个表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）查询

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;

结果如图：

将上面的结果过滤掉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语句执行的结果一样的：

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

看结果:

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

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
https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/