SQL的JOIN连接查询详解

说明：由于MySQL不支持全连接full join，所以实验使用postgresql14。

综述

• 6种类型，8中连接方式。

连接类型	说明
INNER JOIN 内连接	（默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。
LEFT JOIN / LEFT OUTER JOIN 左(外)连接	返回左表中的所有行，即使右表中没有满足条件的行也是如此。
RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接	返回右表中的所有行，即使左表中没有满足条件的行也是如此。
FULL JOIN / FULL OUTER JOIN 全(外)连接	只要其中有一个表存在满足条件的记录，就返回行。
SELF JOIN	将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。
CROSS JOIN	交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。

• 建表语句：

create table user1(id int, user_name varchar(20), addr varchar(20));
create table user2(id int, user_name varchar(20), addr varchar(20));
insert into user1 values(1, 'tangseng', 'user1');
insert into user1 values(2, 'sunwukong', 'user1');
insert into user1 values(5, 'zhubajie', 'user1');
insert into user1 values(6, 'shaseng', 'user1');
insert into user2 values(1, 'sunwukong', 'user2');
insert into user2 values(2, 'niumowang', 'user2');
insert into user2 values(3, 'jiaomowang', 'user2');
insert into user2 values(4, 'pengmowang', 'user2');

user1:

user2:

一、内连接

SQL语句：

select * from
user1 u1 inner join user2 u2
on u1.id = u2.id;

或者

select * from
user1 u1 , user2 u2
where u1.id = u2.id;

结果：

二、外连接

1. 左外连接

• SQL语句：

select * from
user1 u1 left outer join user2 u2
on u1.id =u2.id

结果：

• SQL语句：

select * from 
user1 u1 left outer join user2 u2
on u1.id =u2.id 
where u2.id is null;

结果：

2. 右外连接

• SQL语句：

select * from 
user1 u1 right outer join user2 u2
on u1.id =u2.id ;

结果：

• SQL语句

select * from 
user1 u1 right outer join user2 u2
on u1.id =u2.id 
where u1.id is null;

结果：

3. 全外连接

• SQL语句：

select * from 
user1 u1 full outer join user2 u2
on u1.id =u2.id ;

或者

select * from
user1 u1 left outer join user2 u2
on u1.id =u2.id
union
select * from
user1 u1 right outer join user2 u2
on u1.id =u2.id

结果：

• SQL语句：

select * from 
user1 u1 full outer join user2 u2
on u1.id =u2.id 
where u1.id is null or u2.id is null;

结果：

三、笛卡尔连接（交叉连接）

笛卡尔连接生成笛卡尔积，总行数 = user1行数 x user2行数 ，是用user1表的每一行去组合user2中的每一行，生成一个组合表格。

• SQL语句：

select * from 
user1 u1 cross join user2 u2;

结果：