T-SQL备忘(1):表联接

时间:2023-12-04 17:53:50

测试用例表如下:

T-SQL备忘(1):表联接T-SQL备忘(1):表联接

1.取2个成员表中的交集(A∩B)

T-SQL备忘(1):表联接

T-SQL:

select Member1.Name,Member1.Age from Member1 join Member2 on Member1.Name=Member2.Name
select * from Member1
intersect
select * from Member2

2.取2个成员表中的并集(AB)

T-SQL备忘(1):表联接

T-SQL:

select * from Member1
union
select * from Member2

3.取在A中不在B中(A-B)

T-SQL备忘(1):表联接

T-SQL:

select * from Member1 left join Member2 on Member1.Name=Member2.Name
where Member2.Name is null
---或者使用full join
select * from Member1 full join Member2 on Member1.Name=Member2.Name
where Member2.Name is null

4.取在B中不在A中(B-A

T-SQL:

T-SQL备忘(1):表联接

select * from Member1 right join Member2 on Member1.Name=Member2.Name
where Member1.Name is null
---或者使用full join
select * from Member1 full join Member2 on Member1.Name=Member2.Name
where Member1.Name is null

5.full join = 1+3+4

T-SQL备忘(1):表联接    

T-SQL:

select * from Member1 full join Member2 on Member1.Name=Member2.Name

6.except =3 or 4

T-SQL备忘(1):表联接

T-SQL:

select * from Member1
except
select * from Member2

7.union all(合并包含重复项) =2+1

T-SQL备忘(1):表联接

T-SQL:

select * from Member1
union all
select * from Member2

8.交叉连接 =表A*表B

T-SQL:

select * from Member1 cross join Member2
---等同于
select * from Member1,Member2
select * from Member1 join Member2 on 1=1

一幅简图更好地理解表联接:

T-SQL备忘(1):表联接