集合运算

declare @t1 as table
(
 id int
)
declare @t2 as table
(
 id int
)

insert into @t1(id)
values
(1001),
(1002),
(1003),
(1004),(1004),
(1005),(1005),
(1006),(1006),
(1008),(1008),(1008)

insert into @t2(id)
values
(1001),(1001),
(1002),
(1004),
(1005),(1005),
(1007),(1007),
(1009),(1009),(1009)

select * from @t1 ;
select * from @t2 ;

 

 

inner join (有重复数据)

select a.id
from @t1  as a
inner join @t2 as b on b.id=a.id ;
+-------+
|  id   |
+-------+
| 1001  |
| 1001  |
| 1002  |
| 1004  |
| 1004  |
| 1005  |
| 1005  |
| 1005  |
| 1005  |
+-------+

 

in

select a.id
from @t1  as a
where id in (
select id from @t2
) ;
+-------+
|  id   |
+-------+
| 1001  |
| 1002  |
| 1004  |
| 1004  |
| 1005  |
| 1005  |
+-------+

 

left join (有重复数据) 

select a.id as id1,b.id as id2
from @t1  as a
left join @t2 as b on b.id=a.id ;
+-------+-------+
|  id1  |  id2   |
+-------+-------+
| 1001  | 1001  |
| 1001  | 1001  |
| 1002  | 1002  |
| 1003  | NULL  |
| 1004  | 1004  |
| 1004  | 1004  |
| 1005  | 1005  |
| 1005  | 1005  |
| 1005  | 1005  |
| 1005  | 1005  |
| 1006  | NULL  |
| 1006  | NULL  |
| 1008  | NULL  |
| 1008  | NULL  |
| 1008  | NULL  |
+-------+-------+

   

right join (有重复数据) 

select a.id as id1,b.id as id2
from @t1  as a
right join @t2 as b on b.id=a.id ;
+-------+-------+
|  id1  |  id2   |
+-------+-------+
| 1001  | 1001  |
| 1001  | 1001  |
| 1002  | 1002  |
| 1004  | 1004  |
| 1004  | 1004  |
| 1005  | 1005  |
| 1005  | 1005  |
| 1005  | 1005  |
| 1005  | 1005  |
| NULL  | 1007  |
| NULL  | 1007  |
| NULL  | 1009  |
| NULL  | 1009  |
| NULL  | 1009  |
+-------+-------+

  

union (去重)

select id from @t1
union 
select id from @t2
;
+-------+
|  id   |
+-------+
| 1001  |
| 1002  |
| 1003  |
| 1004  |
| 1005  |
| 1006  |
| 1007  |
| 1008  |
| 1009  |
+-------+

 

 

except (自动去除重复数据)

select id from @t1
except 
select id from @t2
;
+-------+
|  id   |
+-------+
| 1003  |
| 1006  |
| 1008  |
+-------+

not exists 

select id 
from @t1 as a
where not exists ( 
select id from @t2 as b 
where b.id=a.id
)
;
+-------+
|  id   |
+-------+
| 1003  |
| 1006  |
| 1006  |
| 1008  |
| 1008  |
| 1008  |
+-------+

 

 

intersect (自动去除重复数据)

select id from @t1
intersect 
select id from @t2
;
+-------+
|  id   |
+-------+
| 1001  |
| 1002  |
| 1004  |
| 1005  |
+-------+

 exists

select id 
from @t1 as a
where exists ( 
select id from @t2 as b 
where  b.id=a.id
)
;
+-------+
|  id   |
+-------+
| 1001  |
| 1002  |
| 1004  |
| 1004  |
| 1005  |
| 1005  |
+-------+