先创建表和插入数据

CREATE TABLE `a` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`sid` int(11) NOT NULL,

`type` char(10) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `a` (`id`, `sid`, `type`) VALUES (1, 1, 'a');

INSERT INTO `a` (`id`, `sid`, `type`) VALUES (2, 1, 'b');

INSERT INTO `a` (`id`, `sid`, `type`) VALUES (3, 2, 'c');

INSERT INTO `a` (`id`, `sid`, `type`) VALUES (4, 3, 'd');

CREATE TABLE `b` (

`sid` int(11) NOT NULL,

`remark` char(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `b` (`sid`, `remark`) VALUES (1, 'A');

INSERT INTO `b` (`sid`, `remark`) VALUES (2, 'A');

INSERT INTO `b` (`sid`, `remark`) VALUES (3, 'C');

INSERT INTO `b` (`sid`, `remark`) VALUES (4, 'D');

a表

b表

1、对于left join

select * from a left join b on a.sid=b.sid;

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

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

select * from a left join b on a.sid=b.sid and a.type='d';

select * from a left join b on a.sid=b.sid and b.remark ='A';

在使用left join时,on and 和on where会有区别

1. on的条件是在连接生成临时表时使用的条件,以左表为基准 ,不管on中的条件真否,都会返回左表中的记录

2.where条件是在临时表生成好后,再对临时表过滤。此时 和left join有区别(返回左表全部记录),条件不为真就全部过滤掉,on后的条件来生成左右表关联的临时表,where后的条件是生成临时表后对临时表过滤

2、对于inner join

select * from a inner join b on a.sid=b.sid and a.sid=1;

select * from a inner join b on a.sid=b.sid where a.sid=1;

select * from a inner join b on a.sid=b.sid and a.type='d';

select * from a inner join b on a.sid=b.sid where a.type='d';

select * from a inner join b on a.sid=b.sid and b.remark='A';

select * from a inner join b on a.sid=b.sid where b.remark='A';

在使用inner join时,on and 和on where没有区别

在使用inner join时，不管是对左表还是右表进行筛选，on and和on where都会对生成的临时表进行过滤

总结：

on and是进行韦恩运算时 连接时就做的动作,where是全部连接完后，再根据条件过滤