【03】MySQL JOIN,以及on和where条件的区别

0、准备 两张表left_table ，right_table ，初始数据

create table left_table (
    id     int auto_increment
        primary key,
    name   varchar(32) default '' not null comment '姓名',
    status int                    null comment '状态'
) comment '左表';

create table right_table (
    id     int auto_increment
        primary key,
    status int                    null comment '状态',
    status_name   varchar(32) default '' not null comment '状态名'
) comment '右表';

INSERT INTO study.left_table (name, status) VALUES ('A', 10);
INSERT INTO study.left_table (name, status) VALUES ('B', 20);
INSERT INTO study.left_table (name, status) VALUES ('C', 20);
INSERT INTO study.left_table (name, status) VALUES ('D', 40);

INSERT INTO study.right_table (status, status_name) VALUES (10, '离线');
INSERT INTO study.right_table (status, status_name) VALUES (20, '在线');
INSERT INTO study.right_table (status, status_name) VALUES (30, '外出');

1、笛卡尔积 cross join（交叉连接）

笛卡尔积就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录。

select * from left_table a,right_table b;
-- cross join 
select * from left_table a cross join right_table b;
-- 等价于
select * from left_table a inner join right_table b

inner join 不加on条件也是笛卡尔积

2、内连接 inner join （交集）

从笛卡尔积的角度讲就是从笛卡尔积中挑出ON子句条件成立的记录

select * from left_table a,right_table b where a.status=b.status;
-- 等价于
select * from left_table a inner join right_table b on a.status=b.status;

select * from left_table a,right_table b where a.status=b.status;
上面这种写法加上where条件等价于内连接，建议用inner join的写法

3、左连接 left join

从笛卡尔积中挑出ON子句条件成立的记录，然后加上左表中剩余的记录

select * from left_table a left join right_table b on a.status=b.status;

4、右连接 right join

从笛卡尔积中挑出ON子句条件成立的记录，然后加上右表中剩余的记录

select * from left_table a left join right_table b on a.status=b.status;

5、ON和WHERE

1. 数据库在通过连接两张或多张表返回记录时，都会生成一张中间的临时表，然后将临时表返回给用户
2. 对于left join不管on后面跟什么条件，左表的数据都会全部查出来，因此想过率需要把条件放在where后面。
3. 对于inner join，满足on后面的条件才能查出，可以起到过滤作用。
4. where条件是在临时表生成好后，再对临时表进行过滤的条件。

select * from left_table a left join right_table b
on a.status=b.status
and a.name = 'A' ;

select * from left_table a left join right_table b
on a.status=b.status
where a.name = 'A' ;

select * from left_table a inner join right_table b
on a.status=b.status
and a.name = 'A' ;

select * from left_table a inner join right_table b
on a.status=b.status
and b.status=20;

可以看出inner join后面不管是左表还是右表的数据都会被过滤，
使用inner join建议把筛选条件放在on后面

【参考】https://www.cnblogs.com/fudashi/p/7491039.html
【参考】https://blog.csdn.net/itguangit/article/details/83011781