结构化查询语言/SELECT：连接操作
数据应该以不包含冗余信息的方式存储在数据库中。例如，如果我们的数据库包含一群人，他们都拥有相同的爱好，那么我们最好避免在每个记录中重复存储关于某个爱好的相同静态信息。也就是在每个关于爱好者的记录中。同样地，我们也应该避免在每个关于爱好者的记录中重复存储关于某个爱好者的相同详细信息。相反，我们创建独立的person和hobby表，并在两者之间建立联系。这种将数据分组到独立的、无冗余的表中的技术被称为数据库规范化。这种分离也有助于简化逻辑，提高为特定目的组装所需精确项的灵活性。这种组装通过'JOIN'操作来完成。
在我们的示例数据库中，有两个表：person和contact。contact表包含person_id列，它与person表的Primary-Key列id相关联。通过评估列值，我们可以将联系人和人员连接在一起。
|
person 表 P
|
contact 表 C
|
连接（虚拟）表，由person和contact表创建
| P.ID | P.LASTNAME | P.FIRSTNAME | ... | C.ID | C.PERSON_ID | C.CONTACT_TYPE | C.CONTACT_VALUE |
|---|---|---|---|---|---|---|---|
| 1 | Goldstein | Larry | ... | 1 | 1 | 固定电话 | 555-0100 |
| 1 | Goldstein | Larry | ... | 2 | 1 | 电子邮件 | [email protected] |
| 1 | Goldstein | Larry | ... | 3 | 1 | 电子邮件 | lg@my_company.xx |
| 1 | Goldstein | Larry | ... | 4 | 1 | icq | 12111 |
| 2 | Burton | Tom | ... | ? | ? | ? | ? |
| 3 | Hamilton | Lisa | ... | ? | ? | ? | ? |
| 4 | Goldstein | Kim | ... | 5 | 4 | 固定电话 | 5550101 |
| 4 | Goldstein | Kim | ... | 6 | 4 | 手机 | 10123444444 |
| ... | ... | ... | ... | ... | ... | ... | ... |
因此，Larry Goldstein 在存储的person表中只出现一次，现在在连接的虚拟表中出现了四次，每次都与他的四个联系项中的一个组合在一起。Kim Goldstein 和他的两个联系项也是如此。
但是，Tom Burton 和 Lisa Hamilton 的联系信息在哪里？我们可能会在尝试将他们的person数据与他们不存在的contact信息连接时遇到一些麻烦。目前，我们用问号标记了这种情况。关于如何将问题转化为解决方案的详细说明将在本页后面的内容中给出。
显然，使用 JOIN 操作需要指定两件事
- 相关表的名称
- 相关列的名称
基本语法用这两个元素扩展了 SELECT 命令
SELECT <things_to_be_displayed> -- as usual
FROM <tablename_1> <table_1_alias> -- a table alias
JOIN <tablename_2> <table_2_alias> ON <join condition> -- the join criterion
... -- optionally all the other elements of SELECT command
;
让我们先尝试一下。
SELECT *
FROM person p
JOIN contact c ON p.id = c.person_id;
一个表名在 FROM 关键字后面引用（如前所示），另一个表名在新的关键字 JOIN 后面引用，它（毫不奇怪）指示 DBMS 执行连接操作。接下来，ON 关键字引入列名以及比较运算符（或者后面会看到的通用条件）。列名以表的别名 p 和 c 为前缀。这是必要的，因为具有相同名称的列（如 id）可能存在于多个表中。
当 DBMS 执行该命令时，它会提供“某些内容”，其中包含来自两个表的所以列，包括来自各自表（person 和 contact）的两个 id 列。结果包含九行，每行对应一个现有的人员和联系组合；也就是，由于'ON'表达式的存在，没有对应联系记录的人员记录将不会出现在结果中。
提供的“某些内容”看起来像一个新表；事实上，它具有与表相同的结构、行为和数据。如果它是由视图或作为子选择的结果创建的，我们甚至可以在它上面执行新的 SELECT。但它与表之间有一个重要的区别：它组装的数据未存储在 DBMS 中；相反，数据是在运行时从真实表的数值计算出来的，并且只在 DBMS 运行你的程序时保存在临时内存中。
这个关键特性 - 从简单表中组装复杂信息 - 是通过两个简单的关键字 JOIN 和 ON 实现的。正如你将看到的，语法可以扩展为构建非常复杂的查询，这样你就可以在连接条件的规范中添加许多额外的改进。
当结果不符合你的预期时，有时会令人困惑。如果出现这种情况，请尝试简化你的查询，如这里所示。困惑通常源于 JOIN 语法本身可能会变得相当复杂。此外，连接可以与 SELECT 命令的所有其他语法元素结合使用，这也会导致缺乏清晰度。
以下示例显示了连接语法与其他语言元素的结合。
--
-- show only important columns
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id;
-- show only desired rows
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
-- apply any sort order
SELECT p.firstname, p.lastname, c.contact_type as "Kind of Contact", c.contact_value as "Call Number"
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile')
ORDER BY p.lastname, p.firstname, c.contact_type DESC;
-- use functions: min() / max() / count()
SELECT count(*)
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type IN ('fixed line', 'mobile');
-- JOIN a table with itself. Example: Search different persons with the same lastname
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname -- for the second incarnation of person we must use a different alias
WHERE p1.id != p2.id
-- sorting of p2.lastname is not necessary as it is identical to the already sorted p1.lastname
ORDER BY p1.lastname, p1.firstname, p2.firstname;
-- JOIN more than two tables. Example: contact information of different persons with same lastname
SELECT p1.id, p1.firstname, p1.lastname, p2.id, p2.firstname, p2.lastname, c.contact_type, c.contact_value
FROM person p1
JOIN person p2 ON p1.lastname = p2.lastname
JOIN contact c ON p2.id = c.person_id -- contact info from person2. p1.id would lead to person1
WHERE p1.id != p2.id
ORDER BY p1.lastname, p1.firstname, p2.lastname;
在本页的前面，我们看到了一个连接结果的示例，其中一些行包含人员姓名，但没有联系信息 - 相反，在后面的列中显示了一个问号。如果使用了 JOIN 操作的基本语法，那么那些（问号）行将被过滤掉。这种（基本语法，具有排他性结果）被称为内部连接。还有三种不同的外部连接。外部连接的结果不仅包含内部连接结果中所有完整数据行，还包含部分数据行，即在两个存储表中都没有找到数据的那些行；因此，它们被称为左外部连接、右外部连接和全外部连接。
因此，我们可以将基本 JOIN 语法扩展到四个选项
- [INNER] JOIN
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
- FULL [OUTER] JOIN
方括号 [ ] 中的关键字是可选的。解析器从 LEFT、RIGHT 或 FULL 推断出 OUTER，而普通的（即基本语法）JOIN 默认为 INNER。
内部连接可能是四种类型中最常用的。正如我们所见，它只生成完全匹配 ON 后面的条件的那些行。以下是一个示例，展示了如何创建一个包含人员及其联系人的列表。
-- A list of persons and their contacts
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
JOIN contact c ON p.id = c.person_id -- identical meaning: INNER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
最重要的是，没有联系信息的记录不包含在结果中。
有时我们需要更多；例如，我们可能需要一个包含所有人员记录的列表，包括可能为此人员提供的所有联系信息记录。注意，这与上面的示例不同：这次，结果将包含所有人员记录，即使是那些没有联系信息记录的人员记录。
-- A list of ALL persons plus their contacts
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id -- identical meaning: LEFT OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
在联系信息不可用的情况下，DBMS 会用“空值”或“空特殊标记”来代替（不要与字符串（-类型）“空值”或“null”或二进制 0 混淆。然而，实现细节在这里并不重要。空特殊标记将在后面的章节中讨论）。
总之，左（外）连接是一个内部连接，加上每个左侧匹配项在右侧没有对应项的行。
请考虑“左”这个词。它指的是公式左侧的“FROM <table_1> LEFT JOIN <table_2>”，更准确地说，是指左侧表示的表（此处：table_1）；表示该表的每一行都将在结果中至少出现一次，无论在右侧表（此处：table_2）中是否找到相应的记录。
另一个例子
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM contact c
LEFT JOIN person p ON p.id = c.person_id -- identical meaning: LEFT OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
有什么区别？我们改变了表名的顺序。请注意，我们仍然使用 LEFT 连接，但由于contact现在是“左侧”参照物（FROM 子句中的对象），因此contact数据将被认为是主要重要的；因此，所有联系行都将出现在结果中 - 以及人员表中可能存在的任何相关信息。碰巧的是，在我们使用的数据库中，每个联系记录都对应一个人员记录，因此在这种情况下，结果等同于使用内部连接时的情况。但它们与上一个左连接示例的结果不同。
右联接遵循与左联接相同的规则，但方向相反。现在，联接子句中引用的表中的所有记录都将出现在结果中，包括在其他表中没有对应记录的记录。同样，DBMS 会用空值特殊标记填充每个空的右侧列单元格。唯一的区别是，表的评估顺序是反向的，或者换句话说，两个表的交换角色。
-- A list of ALL contact records with any corresponding person data, even if s
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
RIGHT JOIN contact c ON p.id = c.person_id -- same as RIGHT OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
全联接检索左表和右表中的所有行，无论在相应的相反表中是否存在对应记录。
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
FULL JOIN contact c ON p.id = c.person_id -- identical meaning: FULL OUTER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
给定下面的table_1和table_2，
|
table_1
|
table_2
|
全联接
SELECT *
FROM table_1 t1
FULL JOIN table_2 t2 ON t1.id = t2.table_1_id;
将产生
| T1.ID | T1.X | T2.ID | T2.TABLE_1_ID | T2.Y |
|---|---|---|---|---|
| 1 | 11 | 1 | 1 | 21 |
| 2 | 12 | null | null | null |
| 3 | 13 | null | null | null |
| null | null | 2 | 5 | 22 |
这些结果包含（单个）匹配行，加上每个原始表中所有其他记录的每一行。由于这些其他行中的每一行都代表仅在一个表中找到的数据，因此它们都缺少一些数据，因此代表该缺失数据的单元格包含空值特殊标记。
注意：并非所有 DBMS 都支持全联接。但是，因为它不是一个原子操作，所以总是可以通过多个带有 SET 操作的 SELECT 的组合来创建所需的结果。
使用内联接，可以省略 ON。SQL 将其解释为 - 语法上正确的 - 将左表中的每条记录与右表中的每条记录组合的请求。它将返回大量行：两个表的行数的乘积。
这种特殊的内联接称为笛卡尔积或 CROSS JOIN。笛卡尔积是关系代数的基本运算，它是所有 RDBMS 实现的基础。
-- all persons combined with all contacts (some implementations replace the
-- keyword 'JOIN' with a comma)
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM person p
JOIN contact c -- missing ON keyword: p X c will be created
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;
-- count the resulting rows
SELECT count(*)
FROM person p
JOIN contact c;
因此要小心；如果您无意中省略了 ON 项，结果将比预期的大得多。例如，如果第一个表包含 10,000 条记录，而第二个表包含 20,000 条记录，则输出将包含 2 亿行。
我们如何创建人员及其爱好的列表？请记住：一个人可以有许多爱好，而几个人可以有相同的爱好。因此，人员与爱好之间没有直接联系。在两个表之间，我们创建了第三个表person_hobby。它包含人员的 id 以及爱好的 id。
我们必须从person到person_hobby，然后从那里到hobby“行走”。
-- persons combined with their hobbies
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
JOIN person_hobby ph ON p.id = ph.person_id
JOIN hobby h ON ph.hobby_id = h.id
ORDER BY p.lastname, p.firstname, h.hobbyname;
请注意，person_hobby表的任何列都不会进入结果。此表仅在中间执行步骤中起作用。即使它的id列也没有兴趣。
有些人没有爱好。由于我们执行了 INNER JOIN，因此他们不在上面的列表中。如果我们想在列表中查看没有爱好的用户，我们必须执行以前的操作：使用 LEFT OUTER JOIN 而不是 INNER JOIN。
-- ALL persons plus their hobbies (if present)
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
LEFT JOIN person_hobby ph ON p.id = ph.person_id
LEFT JOIN hobby h ON ph.hobby_id = h.id
ORDER BY p.lastname, p.firstname, h.hobbyname;
提示：如有必要，我们可以将每种类型的联接与其他每种类型的联接组合成任何所需的顺序，例如：LEFT OUTER 与 FULL OUTER 与 INNER ...。
联接操作的标准并不局限于通常的公式
SELECT ...
FROM table_1 t1
JOIN table_2 t2 ON t1.id = t2.fk
...
首先，我们可以使用任何列，而不仅仅是主键和外键列。在上面的示例之一中，我们使用 lastname 进行联接。Lastname 是字符类型，没有任何键的意义。为了避免性能低下，一些 DBMS 将列的使用限制在具有索引的列。
其次，比较器并不限于等号。我们可以使用任何有意义的操作符，例如数字值的“大于”。
-- Which person has the greater body weight - restricted to 'de Winter' for clarity
SELECT p1.id, p1.firstname as "is heavier", p1.weight, p2.id, p2.firstname as "than", p2.weight
FROM person p1
JOIN person p2 ON p1.weight > p2.weight
WHERE p1.lastname = 'de Winter'
AND p2.lastname = 'de Winter'
ORDER BY p1.weight desc, p2.weight desc;
第三，我们可以使用任意函数。
-- short lastnames vs. long lastnames
SELECT p1.firstname, p1.lastname as "shorter lastname", p2.firstname, p2.lastname
FROM person p1
JOIN person p2 ON LENGTH(p1.lastname) < LENGTH(p2.lastname)
-- likewise ORDER BY can use functions
ORDER BY length(p1.lastname), length(p2.lastname);
显示有 ICQ 号码的人员的姓氏和名字以及 ICQ 号码
SELECT p.id, p.firstname, p.lastname, c.contact_value
FROM person p
JOIN contact c ON p.id = c.person_id
WHERE c.contact_type = 'icq';
显示有 ICQ 号码和固定电话的人员的姓氏和名字以及 ICQ 号码和固定电话号码。您需要两次联接contact表。
SELECT p.id, p.firstname, p.lastname,
c1.contact_value as icq,
c2.contact_value as "fixed line" -- looks like previous, but is different
FROM person p
JOIN contact c1 ON p.id = c1.person_id
JOIN contact c2 ON p.id = c2.person_id -- it's a second (virtual) incarnation of contact table
WHERE c1.contact_type = 'icq' -- from first incarnation
AND c2.contact_type = 'fixed line'; -- from second incarnation
-- In this example of an INNER JOIN we can convert the WHERE part to an additional JOIN criterion.
-- This may clarify the intention of the command. But be careful: This shifting in combination with
-- one of the OUTER JOINs may lead to different results.
SELECT p.id, p.firstname, p.lastname, c1.contact_value as icq, c2.contact_value as "fixed line"
FROM person p
JOIN contact c1 ON p.id = c1.person_id AND c1.contact_type = 'icq'
JOIN contact c2 ON p.id = c2.person_id AND c2.contact_type = 'fixed line';
显示所有人员的姓氏和名字以及（如果有）ICQ 号码
-- To retrieve ALL persons, it's necessary to use a LEFT join.
-- But the first approach is not what we expect! In this example, the LEFT JOIN is evaluated first
-- and creates an intermediate table with null-values in contact_type (eliminate the
-- WHERE clause to see this intermediate result). These rows and all other except the
-- one with 'ICQ' are then thrown away by evaluating the WHERE clause.
SELECT p.id, p.firstname, p.lastname, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id
WHERE c.contact_type = 'icq';
-- It's necessary to formulate the search criterion as part of the JOIN. Unlike with
-- the INNER JOIN in the previous example with (LEFT/FULL/RIGHT) OUTER JOINs it is not possible
-- to shift it to the WHERE clause.
SELECT p.id, p.firstname, p.lastname, c.contact_value
FROM person p
LEFT JOIN contact c ON p.id = c.person_id AND c.contact_type = 'icq';
创建一个包含所有爱好以及相应人员（如果有）的列表
SELECT p.id p_id, p.firstname, p.lastname, h.hobbyname, h.id h_id
FROM person p
RIGHT JOIN person_hobby ph ON p.id = ph.person_id
RIGHT JOIN hobby h ON ph.hobby_id = h.id
ORDER BY h.hobbyname, p.lastname, p.firstname;
三个外联接之一是否可能包含的行数少于相应的内联接？
No.
All four join types contain the same rows with column-matching-values. In addition
outer joins contain rows where column values do not match - if such a situation exists.