SQL Lesson 4: 用JOINs进行多表联合查询

 

到目前为止我们已经学会了如何做一个单表的查询，但是在现实数据库中往往包含一组相关的数据表，这些表一般会符合数据库范式(normalization)[1]. 让我们先了解下关系数据库的范式

数据库范式(normalization)

数据库范式是数据表设计的规范，在范式规范下，数据库里每个表存储的重复数据降到最少（这有助于数据的一致性维护），同时在数据库范式下，表和表之间不再有很强的数据耦合，可以独立的增长 (ie. 比如汽车引擎的增长和汽车的增长是完全独立的). 范式带来了很多好处，但随着数据表的分离，意味着我们要查询多个数据属性时，需要更复杂的SQL语句，也就是本节开始介绍的多表连接技术。这样SQL的性能也会面临更多的挑战，特别是当大数据量的表很多的情况下.

如果一个实体（比如Dog）的属性数据被分散到多个数据表中，我们就需要学习如何通过 JOIN连表技术来整合这些数据并找到我们想要查询的数据项.

用JOINs进行多表联合查询

主键(primary key), 一般关系数据表中，都会有一个属性列设置为 主键(primary key)。主键是唯一标识一条数据的，不会重复复（想象你的身份证号码)。一个最常见的主键就是auto-incrementing integer(自增ID，每写入一行数据ID+1, 当然字符串，hash值等只要是每条数据是唯一的也可以设为主键.

借助主键(primary key)（当然其他唯一性的属性也可以），我们可以把两个表中具有相同 主键ID的数据连接起来（因为一个ID可以简要的识别一条数据，所以连接之后还是表达的同一条数据）（你可以想象一个左右连线游戏）。具体我们用到 JOIN 关键字。我们先来学习 INNER JOIN.

用INNER JOIN 连接表的语法
SELECT column, another_table_column, …
FROM mytable （主表）
INNER JOIN another_table （要连接的表）
      ON mytable.id = another_table.id (想象一下刚才讲的主键连接，两个相同的连成1条)
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起. 两个表中如果通过ID互相找不到的数据将会舍弃。此时，你可以将连表后的数据看作两个表的合并，SQL中的其他语句会在这个合并基础上 继续执行（想一下和之前的单表操作就一样了）.
还有一个理解INNER JOIN的方式，就是把 INNER JOIN 想成两个集合的交集。 

小贴士

INNER JOIN 可以简写做 JOIN. 两者是相同的意思，但我们还是会继续写作 INNER JOIN 以便和后面的 LEFT JOIN， RIGHT JOIN等相比较.

练习

还会用到之前的Movies表，但我们给数据库加了一张表 BoxOffice 存储着市场相关的信息，比如 收视率和销售数量等，这张表里有一个字段Movie_id 和Movies表的 Id是1-对-1的关系. 尝试下用 INNER JOIN 来解决下面的问题吧！.

Table: Movies (Read-Only)

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Finding Nemo	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115
9	WALL-E	Andrew Stanton	2008	104
10	Up	Pete Docter	2009	101
11	Toy Story 3	Lee Unkrich	2010	103
12	Cars 2	John Lasseter	2011	120
13	Brave	Brenda Chapman	2012	102
14	Monsters University	Dan Scanlon	2013	110

Table: Boxoffice (Read-Only)

Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000

Table(表）: Movies

Id	Title	Director	Year	Length_minutes	Movie_id	Rating	Domestic_sales	International_sales
1	Toy Story	John Lasseter	1995	81	1	8.3	191796233	170162503
2	A Bug's Life	John Lasseter	1998	95	2	7.2	162798565	200600000
3	Toy Story 2	John Lasseter	1999	93	3	7.9	245852179	239163000
4	Monsters, Inc.	Pete Docter	2001	92	4	8.1	289916256	272900000
5	Finding Nemo	Finding Nemo	2003	107	5	8.2	380843261	555900000
6	The Incredibles	Brad Bird	2004	116	6	8	261441092	370001000
7	Cars	John Lasseter	2006	117	7	7.2	244082982	217900167
8	Ratatouille	Brad Bird	2007	115	8	8	206445654	417277164
9	WALL-E	Andrew Stanton	2008	104	9	8.5	223808164	297503696
10	Up	Pete Docter	2009	101	10	8.3	293004164	438338580
11	Toy Story 3	Lee Unkrich	2010	103	11	8.4	415004880	648167031
12	Cars 2	John Lasseter	2011	120	12	6.4	191452396	368400000
13	Brave	Brenda Chapman	2012	102	13	7.2	237283207	301700000
14	Monsters University	Dan Scanlon	2013	110	14	7.4	268492764	475066843

练习 do it — 请完成如下任务

1.找到所有电影的线下Domestic_sales和线上销售额 

SELECT * FROM movies
inner join Boxoffice
on  Movies.id=Boxoffice.Movie_id

2.找到所有线上销售额比线下销售大的电影

 SELECT * FROM movies
inner join Boxoffice
on  Movies.id=Boxoffice.Movie_id
where International_sales > Domestic_sales

3.找出所有电影按市场占有率rating倒序排列 

SELECT * FROM movies
inner join Boxoffice
on  Movies.id=Boxoffice.Movie_id
order by Rating desc    

4.每部电影按线上销售额比较,排名最靠前的导演是谁,线上销量多少 

SELECT Director,International_sales  FROM movies
inner join Boxoffice
on  Movies.id=Boxoffice.Movie_id
order by International_sales desc
limit 1

 外连接（OUTER JOINs）

INNER JOIN 只会保留两个表都存在的数据（还记得之前的交集吗），这看起来意味着一些数据的丢失，在某些场景下会有问题.

真实世界中两个表存在差异很正常，所以我们需要更多的连表方式，也就是本节要介绍的左连接LEFT JOIN,右连接RIGHT JOIN 和 全连接FULL JOIN. 这几个 连接方式都会保留不能匹配的行。

用LEFT/RIGHT/FULL JOINs 做多表查询
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

和INNER JOIN 语法几乎是一样的. 我们看看这三个连接方法的工作原理：
在表A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B.反过来,RIGHT JOIN则保留所有B里的行。最后FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行

我们还是可以用集合的图示来描述：

LEFT JOIN

RIGHT JOIN
 

FULL JOIN
 

将两个表数据1-1连接，保留A或B的原有行，如果某一行在另一个表不存在，会用 NULL来填充结果数据。所有在用这三个JOIN时，你需要单独处理 NULL. 关于 NULL 下一节会做更详细的说明

小贴士

这些Join也可以写作 LEFT OUTER JOIN, RIGHT OUTER JOIN, 或 FULL OUTER JOIN, 和 LEFT JOIN, RIGHT JOIN, and FULL JOIN 等价.

练习

我们会用两个新的表雇员表 Employees 和 办公室表 Buildings. 有一些办公室是新的，还没有雇员.

注意：因为我们这个练习DB的限制,只可以用 LEFT JOIN来解决问题.

Table: Employees (Read-Only)

Role	Name	Building	Years_employed
Engineer	Becky A.	1e	4
Engineer	Dan B.	1e	2
Engineer	Sharon F.	1e	6
Engineer	Dan M.	1e	4
Engineer	Malcom S.	1e	1
Artist	Tylar S.	2w	2
Artist	Sherman D.	2w	8
Artist	Jakob J.	2w	6
Artist	Lillia A.	2w	7
Artist	Brandon J.	2w	7
Manager	Scott K.	1e	9
Manager	Shirlee M.	1e	3
Manager	Daria O.	2w	6
Engineer	Yancy I.	null	0
Artist	Oliver P.	null	0

Table: Buildings (Read-Only)

Building_name	Capacity
1e	24
1w	32
2e	16
2w	20

Table(表）: Employees

Role	Name	Building	Years_employed	Building_name	Capacity
Engineer	Becky A.	1e	4	1e	24
Engineer	Dan B.	1e	2	1e	24
Engineer	Sharon F.	1e	6	1e	24
Engineer	Dan M.	1e	4	1e	24
Engineer	Malcom S.	1e	1	1e	24
Artist	Tylar S.	2w	2	2w	20
Artist	Sherman D.	2w	8	2w	20
Artist	Jakob J.	2w	6	2w	20
Artist	Lillia A.	2w	7	2w	20
Artist	Brandon J.	2w	7	2w	20
Manager	Scott K.	1e	9	1e	24
Manager	Shirlee M.	1e	3	1e	24
Manager	Daria O.	2w	6	2w	20
Engineer	Yancy I.	null	0	null	null
Artist	Oliver P.	null	0	null	null

1.找到所有有雇员的办公室(buildings)名字

SELECT DISTINCT building FROM employees where building is not null

2.找到所有办公室和他们的最大容量

SELECT  Building_name,capacity FROM Buildings 

3.找到所有办公室里的所有角色（包含没有雇员的）,并做唯一输出(DISTINCT)

SELECT  DISTINCT Employees.Role,Building_name from Buildings
LEFT JOIN Employees
on Buildings.Building_name=Employees.Building

4.找到所有有雇员的办公室(buildings)和对应的容量

SELECT  Building,Capacity FROM Employees left join  Buildings
on employees.Building=Buildings.Building_name
where Building is not null