What is the difference between INNER JOIN and OUTER JOIN?

技术背景

在数据库操作中，JOIN 操作是用于将多个表中的数据组合在一起的重要手段。INNER JOIN 和 OUTER JOIN 是两种常见的 JOIN 类型，它们在数据组合的方式和结果上有所不同。理解它们的区别对于编写高效、准确的 SQL 查询至关重要。

实现步骤

1. 数据准备

假设有两个表 A 和 B，结构和数据如下：

A    B
-    -
1    3
2    4
3    5
4    6

2. 不同 JOIN 类型的实现

2.1 INNER JOIN

INNER JOIN 返回两个表中满足连接条件的行，即两个表的交集。

-- 方法一
select * from a INNER JOIN b on a.a = b.b;
-- 方法二
select a.*, b.*  from a,b where a.a = b.b;

结果：

a | b
--+--
3 | 3
4 | 4

2.2 LEFT OUTER JOIN

LEFT OUTER JOIN 返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则用 NULL 填充。

-- 方法一
select * from a LEFT OUTER JOIN b on a.a = b.b;
-- 方法二
select a.*, b.*  from a,b where a.a = b.b(+);

结果：

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

2.3 RIGHT OUTER JOIN

RIGHT OUTER JOIN 返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则用 NULL 填充。

-- 方法一
select * from a RIGHT OUTER JOIN b on a.a = b.b;
-- 方法二
select a.*, b.*  from a,b where a.a(+) = b.b;

结果：

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

2.4 FULL OUTER JOIN

FULL OUTER JOIN 返回两个表中的所有行。如果一个表中没有匹配的行，则用 NULL 填充。

select * from a FULL OUTER JOIN b on a.a = b.b;

结果：

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

核心代码

以下是不同 JOIN 类型的 SQL 代码示例：

-- INNER JOIN
select * from a INNER JOIN b on a.a = b.b;

-- LEFT OUTER JOIN
select * from a LEFT OUTER JOIN b on a.a = b.b;

-- RIGHT OUTER JOIN
select * from a RIGHT OUTER JOIN b on a.a = b.b;

-- FULL OUTER JOIN
select * from a FULL OUTER JOIN b on a.a = b.b;

最佳实践

• 优先使用 INNER JOIN：因为 INNER JOIN 是可结合的，数据库优化器有更多的优化选项，可以通过重新排序连接顺序来提高查询性能。
• 注意 WHERE 子句的使用：在使用 LEFT OUTER JOIN 时，如果在 WHERE 子句中对右表的列设置条件，可能会排除未匹配的行，将外连接转换为内连接。可以将条件移到 ON 子句中避免这种情况。

-- 错误示例
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green';

-- 正确示例
SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green';

常见问题

1. Venn 图的局限性

Venn 图常用于解释集合关系，但在解释 SQL JOIN 时存在问题。例如，CROSS JOIN 是笛卡尔积，集合论不适合表示多个相同元素的集合，Venn 图难以准确表示。INNER JOIN 和 OUTER JOIN 在一行匹配多行时，Venn 图也无法很好地表示。

2. 不同数据库的支持情况

• SQLite 不支持 RIGHT OUTER JOIN 和 FULL OUTER JOIN。
• MySQL 不支持 FULL OUTER JOIN。在 MySQL 中可以使用 UNION 来模拟 FULL OUTER JOIN。

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
UNION ALL
SELECT * FROM table2 LEFT JOIN table1 ON table2.id = table1.id
WHERE table1.id IS NULL;

3. 性能问题

INNER JOIN 通常比外连接性能更好，因为优化器对 INNER JOIN 有更多的优化选项。在可能的情况下，尽量使用 INNER JOIN 来满足查询需求。