a left join b on a.id=b.id 以表a为基准,将表a的记录全部显示出来,根据a.id=b.id把b的相关记录显示出来a rigth join b on a.id=b.id 以表b为基准,将表b的记录全部显示出来,根据a.id=b.id把a的相关记录显示出来
declare @b table(id int,name varchar(10)) insert into @a select 1,'a' insert into @a select 2,'b' insert into @a select 3,'c' insert into @b select 1,'a' insert into @b select 2,'b' insert into @b select 3,'c' insert into @b select 4,'d' select * from @a a left join @b b on a.id =b.id ------------- 1 a 1 a 2 b 2 b 3 c 3 c select * from @a a right join @b b on a.id =b.id -------------------------- 1 a 1 a 2 b 2 b 3 c 3 c NULL NULL 4 d
declare @a table(id int,name varchar(10)) declare @b table(id int,name varchar(10)) insert into @a select 1,'a' insert into @a select 2,'b' insert into @a select 3,'c' insert into @b select 1,'a' insert into @b select 2,'b' insert into @b select 3,'c' insert into @b select 4,'d' select * from @a a left join @b b on a.id =b.idselect * from @a a right join @b b on a.id =b.id
--测试数据 if object_id('t1') is not null drop table t1 create table t1 (id int,name varchar(20)) insert into t1 select 1,'a' union all select 2,'b' union all select 3,'c' if object_id('t2') is not null drop table t2 create table t2 (id int,des varchar(20)) insert into t2 select 1,'desa' union all select 2,'desb' union all select 3,'desc'--1 select * from t1 left join t2 on t1.id=t2.id where t1.id=1 --2 select * from t1 left join t2 on t1.id=t2.id and t1.id=1 --3 select * from t1 left join t2 on t1.id=t2.id and t2.id=1 --4 select * from t1 left join t2 on t1.id=1 想想吧~~ 你也可以left 改为 right 测试下 希望对你有帮助
1.连接分为内连接inner join(inner可省略)和外连接。 2.外连接又分为 左外连接left outer join(outer 可省略和右外连接 right outer join(outer 可省略) 3.内连接 Ta inner join Tb on Ta.A=Tb.B (将符合条件的全部呈现出来) Ta left join Tb on Ta.A=Tb.B 显示所有Ta数据,将符合条件的Tb的值呈现出来 ···
以表a为基准,将表a的记录全部显示出来,根据a.id=b.id把b的相关记录显示出来a rigth join b on a.id=b.id
以表b为基准,将表b的记录全部显示出来,根据a.id=b.id把a的相关记录显示出来
insert into @a select 1,'a'
insert into @a select 2,'b'
insert into @a select 3,'c'
insert into @b select 1,'a'
insert into @b select 2,'b'
insert into @b select 3,'c'
insert into @b select 4,'d'
select * from @a a
left join @b b
on a.id =b.id
-------------
1 a 1 a
2 b 2 b
3 c 3 c
select * from @a a
right join @b b
on a.id =b.id
--------------------------
1 a 1 a
2 b 2 b
3 c 3 c
NULL NULL 4 d
declare @b table(id int,name varchar(10))
insert into @a select 1,'a'
insert into @a select 2,'b'
insert into @a select 3,'c'
insert into @b select 1,'a'
insert into @b select 2,'b'
insert into @b select 3,'c'
insert into @b select 4,'d'
select * from @a a
left join @b b
on a.id =b.idselect * from @a a
right join @b b
on a.id =b.id
--测试数据
if object_id('t1') is not null drop table t1
create table t1 (id int,name varchar(20))
insert into t1
select 1,'a' union all
select 2,'b' union all
select 3,'c'
if object_id('t2') is not null drop table t2
create table t2 (id int,des varchar(20))
insert into t2
select 1,'desa' union all
select 2,'desb' union all
select 3,'desc'--1
select * from t1 left join t2 on t1.id=t2.id where t1.id=1
--2
select * from t1 left join t2 on t1.id=t2.id and t1.id=1
--3
select * from t1 left join t2 on t1.id=t2.id and t2.id=1
--4
select * from t1 left join t2 on t1.id=1
想想吧~~
你也可以left 改为 right
测试下
希望对你有帮助
2.外连接又分为 左外连接left outer join(outer 可省略和右外连接 right outer join(outer 可省略)
3.内连接 Ta inner join Tb on Ta.A=Tb.B (将符合条件的全部呈现出来)
Ta left join Tb on Ta.A=Tb.B 显示所有Ta数据,将符合条件的Tb的值呈现出来
···