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)

Table: Boxoffice (Read-Only)

Table(表）: Movies

练习 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_tableON 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)

Table: Buildings (Read-Only)

Table(表）: Employees

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