温习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 空地区