MySQL中的那些Join

一.首先初始化mysql相关的表,及表中的数据
create database alltest ;
use alltest ;
 
create table aa (
   id int(11) ,
   name varchar(50)
);
 
insert into aa ( id , name ) values (1,’aaa’),(2,’bbb’),(3,’ccc’),(4,’ddd’);
 
create table bb (
   id int(11),
   age int(11)
);
 
insert into bb ( id , age ) values (1,11),(2,12),(5,15);
 
 

 

 

二.效果
三.相关查询
1. SELECT * FROM aa;
2. SELECT * FROM bb;
3. 数据分布图:
4.
SELECT * FROM aa JOIN bb ;
SELECT * FROM aa INNER JOIN bb;
5.
SELECT * FROM aa JOIN bb ON aa.id = bb.id ;
SELECT * FROM aa INNER JOIN bb ON aa.id = bb.id;
说明
上面的句子也可以理解为,Select * from aa, bb where aa.id = bb.id;
JOIN进行了隐式转换,可以看出INNER JOIN等价于 JOIN
6.
SELECT * FROM aa LEFT JOIN bb ON aa.id = bb.id;
SELECT * FROM aa LEFT OUTER JOIN bb ON aa.id = bb.id;
说明:Left Join 是Left Outer Join的简写,Left Join默认是Outer属性的
7.
SELECT * FROM aa LEFT JOIN bb ON aa.id = bb.id WHERE bb.id IS NULL;
8.
SELECT * FROM aa RIGHT JOIN bb ON aa.id = bb.id;
9. 
SELECT * FROM aa RIGHT OUTER JOIN bb ON aa.id = bb.id where aa.id is null;
10.
— mysql不支持全连接
— select * from aa full  join bb on aa.id = bb.id 
SELECT * FROM aa LEFT JOIN bb ON aa.id = bb.id
UNION ALL
SELECT * FROM aa RIGHT JOIN bb ON aa.id = bb.id ;
说明
select * from aa full  join bb on aa.id = bb.id 在一些sql编译场景,是可以用的。只不过在mysql不支持;
full join 和 full outer join 也是一样的
11.
— mysql不支持全连接
— select * from aa full  join bb on aa.id = bb.id where aa.id is null or bb.id is null;
SELECT * FROM aa LEFT JOIN bb ON aa.id = bb.id where bb.id is null
UNION ALL
SELECT * FROM aa RIGHT JOIN bb ON aa.id = bb.id where aa.id is null;

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注