MySql JOIN ON AND 和 JOIN ON WHERE的区别

写sql语句当中，经常看到ON 之后加AND，或者先ON之后再WHERE过滤，这两者有什么区别呢，返回的数据行是怎么样的呢?

数据准备

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tab1
-- ----------------------------
DROP TABLE IF EXISTS `tab1`;
CREATE TABLE `tab1`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `size` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tab1
-- ----------------------------
INSERT INTO `tab1` VALUES (1, 10);
INSERT INTO `tab1` VALUES (2, 20);
INSERT INTO `tab1` VALUES (3, 30);

DROP TABLE IF EXISTS `tab2`;
CREATE TABLE `tab2`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `size` int(11) NULL DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tab2
-- ----------------------------
INSERT INTO `tab2` VALUES (1, 10, 'AAA');
INSERT INTO `tab2` VALUES (2, 20, 'BBB');
INSERT INTO `tab2` VALUES (3, 20, 'BBB');

SET FOREIGN_KEY_CHECKS = 1;

假设现在有两张表：
表1 tab1：

id	size
1	10
2	20
3	30

表2 tab2：

size	name
10	AAA
20	BBB
20	CCC

left join on

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

①size=size,tab1 left join tab2 ,这里会返回多少条数据？

select * from tab1 left join tab2 on tab1.size = tab2.size

left join on and

1、加了and 左表都会返回全表数据，加了and右表是有影响的，即满足条件的会查出来，不满足条件的会留空。

2、对于想取得左表全部数据的场景可以考虑使用，而且左表数据不会因为右表的多条匹配记录而重复 。

（1）如果and语句是对左表进行过滤的，那么不管真假都不起任何作用，左表的记录都回返回。

（2）如果and语句是对右表过滤的，那么左表所有记录都返回，右表筛选以后再与左表连接返回。不满足将留空。

select * from tab1 left join tab2 on tab1.size = tab2.size and tab1.size = 100

select * from tab1 left join tab2 on tab1.size = tab2.size and tab2.size = 10

 

left join on where

where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了。

（1）此时相当于inner join on

（2）此时on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'

 

结果集步骤：

1、left join on根据on条件: tab1.size = tab2.size得到临时表

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA
2	20	20	BBB
2	20	20	CCC
3	30	NULL	NULL

2、再通过where 条件：tab2.name=’AAA’对中间表过滤

tab1.id	tab1.size	tab2.size	tab2.name
1	10	10	AAA