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