Mysql中on和where的区别

前言

1. on和where对于inner join是没有区别的，因为inner join取的就是两个的合集
2. 本文只讨论左右连接

测试准备工作

1、表a
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key_id` int(11) NOT NULL,
`aname` char(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、表b
CREATE TABLE `b` (
`key_id` int(11) NOT NULL,
`bname` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、表a数据
INSERT INTO `a` (`id`, `key_id`, `aname`) VALUES (1, 1, 'a1');
INSERT INTO `a` (`id`, `key_id`, `aname`) VALUES (2, 2, 'a2');
INSERT INTO `a` (`id`, `key_id`, `aname`) VALUES (3, 2, 'a22');
INSERT INTO `a` (`id`, `key_id`, `aname`) VALUES (4, 3, 'a3');

4、表b数据
INSERT INTO `b` (`key_id`, `bname`) VALUES (1, 'b1');
INSERT INTO `b` (`key_id`, `bname`) VALUES (2, 'b2');
INSERT INTO `b` (`key_id`, `bname`) VALUES (3, 'b3');
INSERT INTO `b` (`key_id`, `bname`) VALUES (4, 'b4');

a表数据                                                           b表数据

                                 

分析

（1）无条件的left join（记住这个结果，后面的分析可以和这里对比，这样理解起来较为容易）

• sql

select * from a left join b on a.key_id=b.key_id

• sql查询结果

（2）on

• sql（以下两个sql的结果一样）

select * from a left join b on a.key_id=b.key_id and a.key_id=1

select * from a left join b on a.key_id=b.key_id and b.key_id=1

• sql查询结果

• 结论

1. 不管on上的条件是否为真left join时都会返回a表里的数据
2. on条件只会过滤b表里的数据
3. 所以会返回a表所有数据  + 部分b表数据

（3）where

• sql（以下两个sql的结果一样）

select * from a left join b on a.key_id=b.key_id where a.key_id=1

select * from a left join b on a.key_id=b.key_id where b.key_id=1

• sql查询结果

•  结论

1. where条件是在临时表生成好后，再对临时表过滤，条件不为真就全部过滤掉
2. 所以会返回满足这个条件的部分数据