温习sql语句中JOIN的各种操作(SQL2005环境)
为了方便测试，先建二个基本表T_Employee(员工表)，T_Region(地区表)
Sql语句如下:
CREATE TABLE [dbo].[T_Employee](
[ID] [int] NULL,
[Name] [nvarchar](50) NULL,
[RegionID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[T_Region](
[ID] [int] NULL,
[Name] [nvarchar](50) NULL
) ON [PRIMARY]
录入几条测试数据
T_Employee员工的测试数据:
ID Name RegionId
1 Jimmy 1
2 Rose 2
3 Tom 3
4 Mike 4
5 Unknown 9
6 Jessica 0
7 Mary -1
8 Janson 8
NULL ALLEmpty NULL
NULL Empty1 3
NULL Empty2 -2
9 Emplty3 NULL
T_Region地区表的测试数据:
ID Name
1 湖北省
2 上海市
3 广东省
4 山西省
5 杭州市
6 江西省
NULL 空地区
以下是各种测试的语句以及查询结果
1.［JOIN］
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
4 Mike 4 山西省
NULL Empty1 3 广东省
2.[INNER JOIN]
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e INNER JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
4 Mike 4 山西省
NULL Empty1 3 广东省
与1.结果相同
3.[FULL JOIN]
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e FULL JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
4 Mike 4 山西省
5 Unknown NULL NULL
6 Jessica NULL NULL
7 Mary NULL NULL
8 Janson NULL NULL
NULL ALLEmpty NULL NULL
NULL Empty1 3 广东省
NULL Empty2 NULL NULL
9 Emplty3 NULL NULL
NULL NULL 5 杭州市
NULL NULL 6 江西省
NULL NULL NULL 空地区
4.[FULL OUTER JOIN]
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e FULL OUTER JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
4 Mike 4 山西省
5 Unknown NULL NULL
6 Jessica NULL NULL
7 Mary NULL NULL
8 Janson NULL NULL
NULL ALLEmpty NULL NULL
NULL Empty1 3 广东省
NULL Empty2 NULL NULL
9 Emplty3 NULL NULL
NULL NULL 5 杭州市
NULL NULL 6 江西省
NULL NULL NULL 空地区
与3.结果相同
4.［LEFT JOIN］
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e LEFT JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
4 Mike 4 山西省
5 Unknown NULL NULL
6 Jessica NULL NULL
7 Mary NULL NULL
8 Janson NULL NULL
NULL ALLEmpty NULL NULL
NULL Empty1 3 广东省
NULL Empty2 NULL NULL
9 Emplty3 NULL NULL
5.[LEFT OUTER JOIN]
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e LEFT OUTER JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
4 Mike 4 山西省
5 Unknown NULL NULL
6 Jessica NULL NULL
7 Mary NULL NULL
8 Janson NULL NULL
NULL ALLEmpty NULL NULL
NULL Empty1 3 广东省
NULL Empty2 NULL NULL
9 Emplty3 NULL NULL
与4.结果相同
6.［RIGHT JOIN］
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e RIGHT JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
NULL Empty1 3 广东省
4 Mike 4 山西省
NULL NULL 5 杭州市
NULL NULL 6 江西省
NULL NULL NULL 空地区
7.[RIGHT OUTER JOIN]
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e RIGHT OUTER JOIN T_Region as r ON e.regionId=r.Id
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 2 上海市
3 Tom 3 广东省
NULL Empty1 3 广东省
4 Mike 4 山西省
NULL NULL 5 杭州市
NULL NULL 6 江西省
NULL NULL NULL 空地区
与6.结果相同
8.[CROSS JOIN]
select
e.id as EmpId,
e.name as EmpName,
r.id as RegionId,
r.name as RegionName
from T_Employee as e CROSS JOIN T_Region as r
查询结果:
EmpId EmpName RegionId RegionName
1 Jimmy 1 湖北省
2 Rose 1 湖北省
3 Tom 1 湖北省
4 Mike 1 湖北省
5 Unknown 1 湖北省
6 Jessica 1 湖北省
7 Mary 1 湖北省
8 Janson 1 湖北省
NULL ALLEmpty 1 湖北省
NULL Empty1 1 湖北省
NULL Empty2 1 湖北省
9 Emplty3 1 湖北省
1 Jimmy 2 上海市
2 Rose 2 上海市
3 Tom 2 上海市
4 Mike 2 上海市
5 Unknown 2 上海市
6 Jessica 2 上海市
7 Mary 2 上海市
8 Janson 2 上海市
NULL ALLEmpty 2 上海市
NULL Empty1 2 上海市
NULL Empty2 2 上海市
9 Emplty3 2 上海市
1 Jimmy 3 广东省
2 Rose 3 广东省
3 Tom 3 广东省
4 Mike 3 广东省
5 Unknown 3 广东省
6 Jessica 3 广东省
7 Mary 3 广东省
8 Janson 3 广东省
NULL ALLEmpty 3 广东省
NULL Empty1 3 广东省
NULL Empty2 3 广东省
9 Emplty3 3 广东省
1 Jimmy 4 山西省
2 Rose 4 山西省
3 Tom 4 山西省
4 Mike 4 山西省
5 Unknown 4 山西省
6 Jessica 4 山西省
7 Mary 4 山西省
8 Janson 4 山西省
NULL ALLEmpty 4 山西省
NULL Empty1 4 山西省
NULL Empty2 4 山西省
9 Emplty3 4 山西省
1 Jimmy 5 杭州市
2 Rose 5 杭州市
3 Tom 5 杭州市
4 Mike 5 杭州市
5 Unknown 5 杭州市
6 Jessica 5 杭州市
7 Mary 5 杭州市
8 Janson 5 杭州市
NULL ALLEmpty 5 杭州市
NULL Empty1 5 杭州市
NULL Empty2 5 杭州市
9 Emplty3 5 杭州市
1 Jimmy 6 江西省
2 Rose 6 江西省
3 Tom 6 江西省
4 Mike 6 江西省
5 Unknown 6 江西省
6 Jessica 6 江西省
7 Mary 6 江西省
8 Janson 6 江西省
NULL ALLEmpty 6 江西省
NULL Empty1 6 江西省
NULL Empty2 6 江西省
9 Emplty3 6 江西省
1 Jimmy NULL 空地区
2 Rose NULL 空地区
3 Tom NULL 空地区
4 Mike NULL 空地区
5 Unknown NULL 空地区
6 Jessica NULL 空地区
7 Mary NULL 空地区
8 Janson NULL 空地区
NULL ALLEmpty NULL 空地区
NULL Empty1 NULL 空地区
NULL Empty2 NULL 空地区
9 Emplty3 NULL 空地区