SQL Server基础：Join

CREATE TABLE Atable 2 ( 3 S# INT, 4 Sname nvarchar(32), 5 Sage INT, 6 Sfrom nvarchar(8) 7 ) 8 9 10 insert into Atable 11 select 1,N‘刘一‘,18,N‘A‘ union all 12 select 2,N‘钱二‘,19,N‘A‘ union all 13 select 3,N‘张三‘,17,N‘A‘ union all 14 select 4,N‘李四‘,18,N‘A‘ 15 16 17 18 19 CREATE TABLE Btable 20 ( 21 S# INT, 22 Sname nvarchar(32), 23 Sage INT, 24 Sfrom nvarchar(8) 25 ) 26 27 28 insert into Btable 29 select 1,N‘李四‘,18,N‘B‘ union all 30 select 2,N‘tom‘,19, N‘B‘ union all 31 select 3,N‘刘一‘,17,N‘B‘ union all 32 select 4,N‘jack‘,18,N‘B‘ View Code

数据表

1)cross join：交叉连接不需要任何连接条件。两个表的的数据直接进行笛卡尔积运算。

SELECT * FROM Atable as A  cross join Btable as B order by A.S#

2)inner join == join    内连接的功能是，把两个表相关联的记录列出来，必须是相关联的记录。

SELECT * FROM Atable as A  INNER JOIN Btable as B ON A.Sname = B.Sname

3)left outer join == left join   产生表B的完全集，而A表中匹配的则有值，没有匹配的则以null值取代。

SELECT * FROM Atable as A left outer join Btable as B ON A.Sname = B.Sname

4)right outer join == right jion  产生表A的完全集，而B表中匹配的则有值，没有匹配的则以null值取代。

SELECT * FROM Atable as A right outer join Btable as B ON A.Sname = B.Sname

5)full outer  join  == full join   产生A和B的并集。但是需要注意的是，对于没有匹配的记录，则会以null做为值。

SELECT * FROM Atable as A full join Btable as B ON A.Sname = B.Sname 

6)union与union all    区别就是联合查询的时候union会去重，union all不会去重

SELECT Sname FROM Atable  UNION  SELECT Sname FROM  Btable  
SELECT Sname FROM Atable  UNION ALL SELECT Sname FROM  Btable  

 

SQL Server基础：Join

标签：