SQL的各种连接

create table students (c_id int, c_name varchar(20));
create table subjects (c_id int, c_subject varchar(20));

insert into students values (1,'张三'),(2,'李四'),(3,'王五');  -- orcle不支持这种批量插入的写法
insert into subjects values (1,'语文'),(2,'数学'),(null,'英语');

students

c_id	c_name
1	张三
2	李四
3	王五

subjects

c_id	c_subject
1	语文
2	数学
null	英语

 

 

 

 

 

　　内连接（inner join）：返回两表中on条件共同匹配的行

select * from students s join subjects t on s.c_id = t.c_id;

c_id	c_name	c_id	c_subject
1	张三	1	语文
2	李四	2	数学

 

 

 

　　左链接（left join 或 left outer join）：返回左表的所有行以及右表在on条件匹配到的数据，匹配不上时右表显示空值

select * from students s left join subjects t on s.c_id = t.c_id;

c_id	c_name	c_id	c_subject
1	张三	1	语文
2	李四	2	数学
3	王五	null	null

 

 

 

 

　　右链接（right join 或 right outer join）：返回右表中所有行以及左表在on条件下匹配到的数据，匹配不上时左表显示空值

select * from students s right join subjects t on s.c_id = t.c_id;

c_id	c_name	c_id	c_subject
1	张三	1	语文
2	李四	2	数学
null	null	null	英语

 

 

 

 

　　全连接（full join 或 full outer join）：完整外部连接左表和右表中的所有行，匹配不上时另一表显示空值

-- oracle
select * from students s full join subjects t on s.c_id = t.c_id; 

-- mysql，因myql不支持完全连接，直接使用full join会报语法错误，可以通过union将左链接查询和右链接查询的结果合并起来从而得到全连接的效果
select * from students s left join subjects t on s.c_id = t.c_id
union
select * from students s right join subjects t on s.c_id = t.c_id;

c_id	c_name	c_id	c_subject
1	张三	1	语文
2	李四	2	数学
null	null	null	英语
3	王五	null	null

 

 

 

 

 

　　交叉连接（cross join）：左表的每一行分别和右表中的每一行组合，也叫做笛卡尔积

select * from students s cross join subjects t;

 

c_id	c_name	c_id	c_subject
1	张三	1	语文
2	李四	1	语文
3	王五	1	语文
1	张三	2	数学
2	李四	2	数学
3	王五	2	数学
1	张三	null	英语
2	李四	null	英语
3	王五	null	英语