left join 、right join 、inner join 和 full join 的区别

1、先创建数据库

use master
go

if exists( select * from sysdatabases where name='DataTest')
	begin 
		drop database DataTest
	end
else 
	begin 
		create database DataTest
	end

2、创建表

use DataTest 
go

create table A (id int,name  nvarchar(100))
create table B (id int,sex nvarchar(2))
insert into A(id,name)
select '1','徐骁' union 
select '2','徐脂虎' union 
select '3','徐渭熊' union 
select '4','徐凤年' union 
select '5','徐龙象' union 
select '6','张三' union 
select '7','李四' 

insert into B(id,sex)
select '0','男' union
select '1','男' union
select '2','女' union
select '3','女' union
select '4','男' union
select '8','男' union 
select '9','男'

3、left join、right join 、inner join 、full  join 区别

3.1 left join  在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录

select * from A 
left join B on a.id=b.id

3.2 right join  在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录

select * from A 
right join B on a.id=b.id

 

3.3 inner join  在两张表进行连接查询时，只保留两张表中完全匹配的结果集

select * from A 
inner join B on a.id=b.id

 3.4 full join 在两张表进行连接查询时，返回左表和右表中所有没有匹配的行

 查询结果是left join和right join的并集

select * from a
full join b on a.id=b.id