mysql----7种join_mysql的join有几种-优快云博客

本文链接：https://blog.youkuaiyun.com/shen_chengfeng/article/details/104365257

本文深入解析MySQL中的七种连接类型，包括内连接、左连接、右连接、全连接及全外连接等，通过实例演示了如何使用这些连接来查询两个表中共享或独有的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql的连接细分共有七种，分别是：

内连接(A B共有)：

select <1,2>
from A
inner join B
on A.key = B.key;

left join(A独有+A，B共有):

select <1,2>
from A
left join B
on A.key = B.key;

right join(B独有+A，B共有):

select <1,2>
from A
left join B
on A.key = B.key

left join(A独有):

select <1,2>
from A
left join B
on A.key = B.key
 where B.key is null;

right join(A独有):

select <1,2>
 from A
left join B
on A.key = B.key
where A.key is null;

全连接(A+B)：

 select <1,2>
 from A
 full outer join B
 on A.key = B.key;

全外连接(A+B，无AB共有)：

 select <1,2>
from A
full outer join B
 on A.key = B.key
where A.key is null or B.key is null;

create table join_A
(
	id int not null primary key auto_increment,
	name varchar(32) default ''
);


create table join_B
(
	id int not null primary key auto_increment,
	name varchar(32) default ''
);

insert into join_A(name)
value
('nameA1'),
('nameA2'),
('name');

insert into join_B(name)
value
('nameB1'),
('nameB2'),
('name');

#A,B共有
select *
from join_A
inner join join_B
on join_A.name = join_B.name;

#全A
select *
from join_A
left join join_B
on join_A.name = join_B.name;

#全B
select *
from join_A
right join join_B
on join_A.name = join_B.name;

#A独有
select *
from join_A
left join join_B
on join_A.name = join_B.name
where join_B.name is null;

#B独有
select *
from join_A
right join join_B
on join_A.name = join_B.name
where join_A.name is null;

#全外连接
#mysql 不支持full outer join的语法，但是Oracle支持
select *
from join_A
full outer join join_B
on join_A.name = join_B.name;

#语法上不支持，因此换一种方式：A独有 + B独有 + AB共有
select * from join_A left join join_B on join_A.name = join_B.name where join_B.name is null
union 
select * from join_A right join join_B on join_A.name = join_B.name where join_A.name is null
union 
select * from join_A inner join join_B on join_A.name = join_B.name;

#A 和 B独有
select * from join_A left join join_B on join_A.name = join_B.name where join_B.name is null
union 
select * from join_A right join join_B on join_A.name = join_B.name where join_A.name is null;