create table t1(id int identity(1,1), cname nvarchar(20)) insert into t1 select 'a' union all select 'b' union all select 'c' union all select 'd' union all select 'e' create table t2(id int identity(1,1), cname nvarchar(20)) insert into t2 select 'q' union all select 'w' union all select 'e' union all select 'r'select t1.id,t1.cname,t2.cname from t1 inner join t2 on t1.id=t2.id --因为t2只有4条纪录,所以返回4条纪录 ---------------- 1 a q 2 b w 3 c e 4 d rselect t1.id,t1.cname,t2.cname from t1 right join t2 on t1.id=t2.id --右连接,因为右边的表t2只有4条,所以返回4条 ---------------- 1 a q 2 b w 3 c e 4 d rselect t1.id,t1.cname,t2.cname from t1 left join t2 on t1.id=t2.id--右连接,因为左边的表t1有5条纪录,所以返回5条 ------ 1 a q 2 b w 3 c e 4 d r 5 e NULLselect t1.id,t1.cname,t2.cname from t1 full outer join t2 on t1.id=t2.id --包含全部,因为最多的是5条,所以返回5条 --------------- 1 a q 2 b w 3 c e 4 d r 5 e NULL Oracle里面的左右连接一样道理。
insert into t1 select 'a'
union all select 'b'
union all select 'c'
union all select 'd'
union all select 'e'
create table t2(id int identity(1,1), cname nvarchar(20))
insert into t2 select 'q'
union all select 'w'
union all select 'e'
union all select 'r'select t1.id,t1.cname,t2.cname from t1
inner join t2 on t1.id=t2.id
--因为t2只有4条纪录,所以返回4条纪录
----------------
1 a q
2 b w
3 c e
4 d rselect t1.id,t1.cname,t2.cname from t1
right join t2 on t1.id=t2.id
--右连接,因为右边的表t2只有4条,所以返回4条
----------------
1 a q
2 b w
3 c e
4 d rselect t1.id,t1.cname,t2.cname from t1
left join t2 on t1.id=t2.id--右连接,因为左边的表t1有5条纪录,所以返回5条
------
1 a q
2 b w
3 c e
4 d r
5 e NULLselect t1.id,t1.cname,t2.cname from t1
full outer join t2 on t1.id=t2.id
--包含全部,因为最多的是5条,所以返回5条
---------------
1 a q
2 b w
3 c e
4 d r
5 e NULL
Oracle里面的左右连接一样道理。