Head First SQL 第十章

外联接，自联接与联合。P417

关于联接我们只认识了一半。我们还没有见过

外联接：在表中没有匹配记录的情况下返回记录。

自联接：可以联接表本身。

联合：可以合并查询结果。

=========================================================

当你想清理旧数据的时候，可以考虑使用外联接来找出不需要的值。

外联接返回某张表的所有行，并且带有来自另一张表的条件相符的行。

使用  内联接  的时候，要对比来自两张表的行，但是表的顺序没有影响。

先回顾一下：

SEKECT g.girl, t.toy
FROM girls g
INNER JOIN toys t
ON g.toy_id = t.toy_id;

这就是从两张表中取出 toy_id  列同时出现在两张表中的列。

======================================================

LEFT OUTER JOIN 接收左表的所有行，并用这些行与右表中的行匹配。当左表与右表有一对多的关系时，左外联接特别有用。

在 LEFT OUTER JOIN 中，出现在  FROM 后，联接前的表称为左表，联接后的表是右表。

利用左外联接找出每个女孩拥有的玩具。

女孩是左表，toys是右表

SELECT g.girl, t.toy
FROM girls g
LEFT OUTER JOIN toys t
ON g.toy_id = t.toy_id;

===============================================================

外联接与多个相符结果：

虽然在另一个表中没有相符的记录，但是你还是会去的数据行，再匹配出多条记录时就会取出多行。

右外联接：

与左外联接一样，除了是用右表和左表对比：

SELECT g.girl, t.toy
FROM toys t
RIGHT OUTER JOIN girls g
ON g.toy_id = t.toy_id;

左右切换只用改一个单词。

==========================================================

利用外联接：P429

找出小丑的头领

现在数据库模式如图：

可以创建新表：列出每个小丑与他的头领的ID。下是小丑组织图并带有每个小丑的ID。

以及一个小丑头领表：

注意10号就是大BOSS，所以他的boss就是自己。

新表的位置：

再看一下现在的数据库模式，并研究新表如何放入模式中：

左下角就是新表。

新表有一对一关系，就是主键id列，同时又一对多关系——boss_id列。这张表的主键与外键都来自 clown_info 表。

其实我们只要在 clown_info 表中加上新列来记录每个小丑头领的 ID，这一列就是 boss_id，与 clown_id 表中设计得一样。

clown_boss 表中 boss_id 是外键，当我们把这一列加入 clown_info 时，仍然是外键，虽然它也在同一张表中。这叫做自引用外键。

自引用 表示的是它引用的是同一张表内的另一列的键。

连接表与它自己：

列出每个小丑的姓名和他们头领的id：

SELECT name, boss_id FROM clown_info;

但是我们需要小丑的姓名和头领的姓名。

SELECT c1.name, c2.name AS boss
FROM clown_info1 c1
INNER JOIN clown_info2 c2
ON c1.boss_id = c2.id;

这里给 第二列取名为 boss ，不会搞混。

==========================================================

刚刚出现了两张相同的表供联接，但是实际上正常规范数据库相同表不会出现两次。

于是我们改用自联接模拟联接两张表的效果。

SELECT c1.name, c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ONc1.boss_id = c2.id;

不用两张表，而是用 clown_info 两次。

第一次设定别名c1，第二次设定别名c2，然后内联接 c1.boss_id  和c2.id

自联接把单一表当初两张完全相同的表进行查询。

==========================================================

另一种去的多张表内容的方式：

以下为 Greg 提到的三张表：

目前有三条独立的 SELECT 语句：

SELECT title FROM job_cu rrent;
SELECT title FROM job_desired;
SELECT title FROM job_listings;

上述查询没问题，但是我们更想合并一下。

=============================================================

使用 UNION

UNION根据我们在 SELECT 中指定的列，把两张或者更多张表的查询结果合并到一个表中。可以把  UNION的查询结果看做是 “重叠了” 每个 SELECT 的查询结果。

SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings;

然后会发现结果集中没有重复的记录，但是职务没有依序排列，于是我们加上 ORDER BY：P437

SELECT title FROM job_current ORDER BY title
UNION
SELECT title FROM job_desired ORDER BY title
UNION
SELECT title FROM job_listings ORDER BY title;

========================================================

UNION的使用限制

数据库软件可能会报错，因为同事用了多个 ORDER BY

UNION只能接受一个 ORDER BY，而且必须在语句末端。

为此我们要做一些修改：

SELECT title FROM job_current
UNION
SELECT title FROM job_desired
UNION
SELECT title FROM job_listings
ORDER BY title;

==========================================

UNION ALL

方式和 UNION一样，但是会返回列的所有内容：

SELECT title FROM job_current
UNION ALL
SELECT title FROM job_desired
UNION ALL
SELECT title FROM job_listings
ORDER BY title;

目前为止，都是相同数据类型列的UNION，我们可能也要结合不同类的的UNION

数据会试着转换为向荣类型，如果无法转换，查询就失败。

=========================================================

从联合创建表

由 UNION 返回的数据类型不太容易分辨，除非用某种方式捕获类型。使用 CREATE TABLE AS 可以捕获 UNION的结果。

CREATE TABLE AS 接收来自 SELECT 查询的结果，并把结果制作成一张表：

CREATE TABLE my_union AS
SELECT title FROM job_current UNION
SELECT title FROM job_desired
UNION SELECT title FROM job_listings;

===========================================================

INTERSECT 与 EXCEPT

交集与差集

方法和 UNION 基本一样，找出查询结果重叠的部分。

INTERSECT 只会返回同时在第一个与第二个查询中的列。

SELECT title FROM job_current
INTERSECT
SELECT title FROM job_desired;

EXCEPT 返回只出现在第一个查询，而不出现在第二个查询中的列。

SELECT title FROM job_current
EXCEPT
SELECT title FROM job_desired;

=============================================================================

现在轮到子查询和联接的比较了。

之前说过，几乎所有子查询能干的事情，联接都能做。

P444

比如这里有个子查询语句：

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
WHERE jc.title IN (SELECT title FROM job_listings);

现在我们用内联接重写：

SELECT mc.first_name, mc.last_name, mc.phone, jc.title
FROM job_current AS jc NATURE JOIN my_contacts AS mc
INNER JOIN job_listings jl
ON jc.title = jl.title;

问题是我们到底用哪个呢？

===============================================

我们看过子查询如何变成联接，现在看看变成子查询的联接：

SELECT c1.name, c2.name AS boss
FROM clown_info c1
INNER JOIN clown_info c2
ON c1.boss_id = c2.id;

改写关联子查询：P449

SELECT c1.name,
(SELECT name FROM clown_info
WHERE c1.boss_id = id) AS boss
FROM clown_info c1;