联接 (SQL Server)
集运算符 - UNION (Transact-SQL)
● INNER JOIN 或 JOIN: 内联接,两表都有的才筛选出来,两表交集,【INNER】关键字可以省略;
● LEFT JOIN 或 LEFT OUTER JOIN: 左联接,也称左外连接,即使右表中没有匹配,也从左表返回所有的行,【OUTER】关键字可以省略;
● RIGHT JOIN 或 RIGHT OUTER JOIN: 右联接,也称右外连接,即使左表中没有匹配,也从右表返回所有的行,【OUTER】关键字可以省略;
● FULL JOIN 或 FULL OUTER JOIN: 全外联接,只要其中一个表中存在匹配,就返回行,两边都列出来,保留两张表中所有的记录,能匹配就匹配,不匹配的用NULL列出,【OUTER】关键字可以省略;
● CROSS JOIN:交叉联接,列出两边所有组合,也叫笛卡尔集A.Rows * B.Rows
● union 是一组集合操作,用于将两个select语句的结果组合到一个结果集中,该结果集包括属于union中select语句的所有行
union:删除重复行【排除重复行】;
union all:在最终结果集中包含重复行【包括重复行】。
一、inner join
select city.name as 城市, area.name as 地区 from area
inner join city
on city.code = area.cityid
或者
select c.name,a.name from area as a
inner join city as c
on c.code=a.cityid
二、left join
select city.name as 城市,area.name as 地区 from area
left join city
on city.code = area.cityid
或者
select c.name as 城市,a.name as 地区 from area as a
left join city as c
on c.code = a.cityid
三、right join
select city.name as 城市,area.name as 地区 from area
right join city
on city.code = area.cityid
或者
select c.name as 城市,a.name as 地区 from area as a
right join city as c
on c.code = a.cityid
四、full join
select city.name,area.name from area
full join city
on city.code = area.cityid
--或者
select c.name,a.name from area as a
full join city as c
on c.code = a.cityid
表结构
City表
[sql] view plaincopyprint?
- CREATE TABLE [dbo].[city](
- [id] [int] NOT NULL,
- [code] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL,
- [name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [provinceId] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL
- ) ON [PRIMARY]
CREATE TABLE [dbo].[city](
[id] [int] NOT NULL,
[code] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL,
[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[provinceId] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
Area表
[sql] view plaincopyprint?
- CREATE TABLE [dbo].[area](
- [id] [int] NOT NULL,
- [code] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
- [name] [nvarchar](60) COLLATE Chinese_PRC_CI_AS NULL,
- [cityId] [nvarchar](6) COLLATE Chinese_PRC_CI_AS NULL
- ) ON [PRIMARY]