sql关联查询条件在where 和 on的区别小记

猜测:

关联查询条件写在on后面，先过滤再关联，关联条件是正常的关联

where后面，先关联，在查询

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `s1` int(11) DEFAULT NULL,
  `s2` char(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t1` VALUES ('100', 'abcde');
INSERT INTO `t1` VALUES ('101', 'bcdef');
INSERT INTO `t1` VALUES ('103', 'cdefg');

CREATE TABLE `t2` (
  `s1` char(5) DEFAULT NULL,
  `s2` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `t2` VALUES ('abcde', 'aa');
INSERT INTO `t2` VALUES ('bcdef', null);

SELECT * FROM t1 left join t2 on t1.s2=t2.s1 and t2.s2 is not null;

s1	s2	s11	s21
100	abcde	abcde	aa
101	bcdef		
103	cdefg		

-- 类似于
SELECT * FROM t1 left join (select * from t2 s2 is not null) on t1.s2=t2.s1;

 

SELECT * FROM t1 left join t2 on t1.s2=t2.s1 where t2.s2 is not null;


s1	s2	s11	s21
100	abcde	abcde	aa

以上