七月 11, 2023
与几乎所有关系数据库一样，Oracle 允许生成从两个或多个表合并或 JOIN 行以创建最终结果集的查询。虽然可以执行多种类型的连接，但最常见的是 INNER JOIN 和 OUTER JOIN。
什么是内连接？
关系数据库中的INNER JOIN只是两个或多个表的连接，返回的结果将仅包含满足所有连接条件的数据。
例如，这里我们有一个基础的模式library，包含两个表：books和languages。表languages只是可能的语言名称的列表，每条记录有唯一的语言id：
SELECT * FROM library.languages;
id | name
----|----------
1 | English
2 | French
3 | German
4 | Mandarin
5 | Spanish
6 | Arabic
7 | Japanese
8 | Russian
9 | Greek
10 | Italian
同时，我们的表books有一个language_id的列，对于大多数（但不是全部）书籍来说，它只包含与书籍的原始出版语言相关的language_id：
SELECT * FROM
books
ORDER BY
id
FETCH FIRST 10 ROWS ONLY;
id | title | author | year_published | language_id
----|------------------------|---------------------|----------------|----------------
1 | In Search of Lost Time | Marcel Proust | 1913 | 2
2 | Ulysses | James Joyce | 1922 | 1
3 | Don Quixote | Miguel de Cervantes | 1605 | 5
4 | Moby Dick | Herman Melville | 1851 | 1
5 | Hamlet | William Shakespeare | 1601 | (null)
6 | War and Peace | Leo Tolstoy | 1869 | 8
7 | The Odyssey | Homer | -700 | 9
8 | The Great Gatsby | F. Scott Fitzgerald | 1925 | 1
9 | The Divine Comedy | Dante Alighieri | 1472 | 10
10 | Madame Bovary | Gustave Flaubert | 1857 | 2
在许多情况下，我们可能希望对表books和languages执行INNER JOIN，以便查看每本书的language name值，而不是查看无意义的language_id。
SELECT
b.id,
b.title,
b.author,
b.year_published,
l.name language
FROM
books b
INNER JOIN
library.languages l
ON
b.language_id = l.id
ORDER BY
b.id
FETCH FIRST 10 ROWS ONLY;
id | title | author | year_published | language
----|------------------------|---------------------|----------------|---------
1 | In Search of Lost Time | Marcel Proust | 1913 | French
2 | Ulysses | James Joyce | 1922 | English
3 | Don Quixote | Miguel de Cervantes | 1605 | Spanish
4 | Moby Dick | Herman Melville | 1851 | English
6 | War and Peace | Leo Tolstoy | 1869 | Russian
7 | The Odyssey | Homer | -700 | Greek
8 | The Great Gatsby | F. Scott Fitzgerald | 1925 | English
9 | The Divine Comedy | Dante Alighieri | 1472 | Italian
10 | Madame Bovary | Gustave Flaubert | 1857 | French
11 | The Brothers Karamazov | Fyodor Dostoyevsky | 1880 | Russian
这里需要注意的是，上述两个查询返回的结果集略有不同。在第一个中，我们只是列出了前10本书，但在INNER JOIN查询中，我们只返回两个表中满足所有条件的结果。因此，Hamlet（其language_id值为null或空）的记录将被忽略，并且不会在我们INNER JOIN的结果中返回。
什么是外连接？
OUTER JOIN不仅会返回满足所有连接条件的结果，还返回一个表中不满足条件的的行，而不是INNER JOIN那样只返回满足所有连接条件的结果。被选择为“绕过”条件要求的表由连接的方向性或“侧边”确定，通常称为LEFT或RIGHT外连接。
在定义OUTER JOIN的侧边时，您正在指定哪个表将始终返回其行，即使连接另一侧的相对表的列（作为连接条件的一部分）缺失值或为null。
因此，如果我们执行与上述相同的基本JOIN来检索books和language names，我们知道我们的books表应该始终返回数据，所以我们的JOIN这边应该“指向”我们的表books，从而使表languages成为我们关联到它的OUTER表。
为此，我们只需要将：
books b INNER JOIN library.languages l
… 更改为：
books b LEFT OUTER JOIN library.languages l
因此，整个查询和结果集看起来几乎与INNER JOIN相同，只是稍作改动：
SELECT
b.id,
b.title,
b.author,
b.year_published,
l.name language
FROM
books b
LEFT OUTER JOIN
library.languages l
ON
b.language_id = l.id
ORDER BY
b.id
FETCH FIRST 10 ROWS ONLY;
id | title | author | year_published | language
----|------------------------|---------------------|----------------|----------
1 | In Search of Lost Time | Marcel Proust | 1913 | French
2 | Ulysses | James Joyce | 1922 | English
3 | Don Quixote | Miguel de Cervantes | 1605 | Spanish
4 | Moby Dick | Herman Melville | 1851 | English
5 | Hamlet | William Shakespeare | 1601 | (null)
6 | War and Peace | Leo Tolstoy | 1869 | Russian
7 | The Odyssey | Homer | -700 | Greek
8 | The Great Gatsby | F. Scott Fitzgerald | 1925 | English
9 | The Divine Comedy | Dante Alighieri | 1472 | Italian
10 | Madame Bovary | Gustave Flaubert | 1857 | French
正如预期的那样，通过使用LEFT OUTER JOIN而不是之前的INNER JOIN，我们得到了两全其美的结果：我们不会仅仅因为该记录的language_id值是null而跳过任何books记录（例如Hamlet），但对于存在language_id的所有记录，我们从languages表中得到了格式良好的language name。
使用加号 (+) 进行外连接
如官方文档所示，Oracle 提供了一个特殊的外连接运算符（符号+），它是执行OUTER JOINS的简写。
实际上，符号+直接放置在条件语句中，且在可选表（允许在条件中包含空或null值的表）的一侧。
因此，我们可以再次使用运算符+重写上面的LEFT OUTER JOIN语句，如下所示：
SELECT
b.id,
b.title,
b.author,
b.year_published,
l.name language
FROM
books b,
library.languages l
WHERE
l.id (+)= b.language_id
ORDER BY
b.id
FETCH FIRST 10 ROWS ONLY;
结果与上面标准的LEFT OUTER JOIN示例相同，因此我们不会在此处包含它们。但是，关于使用运算符+进行OUTER JOIN的语法，有一个关键方面需要注意。
运算符+必须位于条件的左侧（等号=的左侧）。因此，在这种情况下，因为我们希望确保我们的languages表是可以在此比较期间返回null值的可选表，因此我们交换了此条件中表的顺序，因此languages在左侧（并且是可选的），而books在右侧。
最后，由于使用运算符+时条件中连接侧边的这种重新调整，重要的是要意识到上述内容只是RIGHT OUTER JOIN的简写。这意味着查询中的以下代码段：
FROM
books b,
library.languages l
WHERE
l.id (+)= b.language_id
… 实际上等同于：
FROM
library.languages l
RIGHT OUTER JOIN
books b
ON
b.language_id = l.id
PostgreSQL 的外连接
在 PostgreSQL 的 SELECT 查询中，支持使用LEFT [ OUTER ] JOIN或RIGHT [ OUTER ] JOIN进行外连接，但是不支持使用加号 (+) 进行外连接。所以，在从 Oracle 迁移到 PostgreSQL 时，需要将加号 (+) 形式的外连接，转换成LEFT [ OUTER ] JOIN或RIGHT [ OUTER ] JOIN形式的外连接。