SQL 随笔记: 常见的表连接方式

表连接（Join）是数据库中常见的操作，用于将多个表的数据根据某个条件结合在一起，以获取满足条件的结果集。不同的连接方式会影响查询的结果集，了解每种连接的特性和应用场景有助于高效地获取所需数据。以下是几种常见的表连接方式：

1. 内连接（INNER JOIN）

• 内连接 是最常用的连接方式，返回两个表中符合条件的匹配记录。如果某一行在任意一个表中没有匹配项，则不会包含在结果中。INNER JOIN 是 SQL 中的默认连接类型，即不指定连接类型时数据库通常会默认执行内连接。

• 特点：只返回匹配的记录。

  示例语法：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  INNER JOIN 表2 AS b 
  ON a.列名 = b.列名;
  

  或者：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  JOIN 表2 AS b 
  ON a.列名 = b.列名;  -- JOIN 是 INNER JOIN 的简写
  

  • 适用场景：当只需要返回两个表中都存在的匹配数据时使用。

2. 外连接（OUTER JOIN）

外连接根据数据匹配情况，将某些表中的数据无条件保留，具体分为三种类型：左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL JOIN）。

2.1 左外连接（LEFT JOIN）

• 返回左表中的所有记录，即使在右表中没有匹配项，右表没有匹配的数据将显示为 NULL。左外连接确保左表的所有行都出现在结果集中，不管右表是否有对应的匹配记录。

  示例语法：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  LEFT JOIN 表2 AS b 
  ON a.列名 = b.列名;
  

  或者：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  LEFT OUTER JOIN 表2 AS b 
  ON a.列名 = b.列名;  -- OUTER 可以省略
  

  • 适用场景：当需要返回左表中的所有数据，无论其在右表中是否有匹配时使用。

2.2 右外连接（RIGHT JOIN）

• 与左外连接相反，返回右表中的所有记录，即使左表中没有匹配项。右外连接确保右表的所有行出现在结果集中，即使左表没有匹配的记录。

  示例语法：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  RIGHT JOIN 表2 AS b 
  ON a.列名 = b.列名;
  

  或者：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  RIGHT OUTER JOIN 表2 AS b 
  ON a.列名 = b.列名;  -- OUTER 可以省略
  

  • 适用场景：当需要返回右表中的所有数据时使用。

2.3 全外连接（FULL JOIN）

• 返回两个表中的所有记录，如果没有匹配项，未匹配的一方数据列显示为 NULL。全外连接会保留左表和右表中的所有记录。在不支持 FULL JOIN 的数据库（如 MySQL）中，可以通过 UNION 两个查询来实现，如 LEFT JOIN 和 RIGHT JOIN 的联合。特别地，UNION 默认去除重复项，如果希望保留所有记录，可以使用 UNION ALL。

  示例语法：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  FULL JOIN 表2 AS b 
  ON a.列名 = b.列名;
  

  或者（在不支持 FULL JOIN 的数据库中）：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  LEFT JOIN 表2 AS b 
  ON a.列名 = b.列名
  UNION
  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  RIGHT JOIN 表2 AS b 
  ON a.列名 = b.列名;
  

  • 适用场景：当需要返回两个表中的所有数据时，无论匹配情况。

3. 交叉连接（CROSS JOIN）

• 交叉连接 返回两个表的笛卡尔积，即没有条件地将每个表的每一条记录组合起来。结果集的行数等于两个表的行数相乘，这意味着结果集的规模可能非常大。交叉连接生成的组合较多，因此在数据量大的表上使用时需要特别小心，避免产生大量冗余数据。

  示例语法：

  SELECT a.列名, b.列名 
  FROM 表1 AS a 
  CROSS JOIN 表2 AS b;
  

  • 适用场景：当需要生成所有可能的组合时使用，典型应用包括测试数据生成或数据分析中的所有组合情况。注意：谨慎使用交叉连接，以免导致性能问题。

4. 自连接（SELF JOIN）

• 自连接 用于将一个表与自身进行连接，特别适用于处理层级结构（如员工和经理之间的关系），或对同一表中的不同行进行比较。自连接通常用于处理表中层次化的关系或复杂的自身比较，例如查询员工与其经理的关系。

  示例语法：

  SELECT a.列名 AS Employee, b.列名 AS Manager 
  FROM 表名 AS a 
  INNER JOIN 表名 AS b 
  ON a.列名 = b.列名;
  

  • 适用场景：当需要对同一表中的不同记录进行比较时使用，如在同一张表中查询员工与其经理的关系或处理其他层次化数据。

5. 自然连接（NATURAL JOIN）

• 自然连接 根据两个表中相同名称的列自动进行匹配连接，不需要显式指定 ON 条件。这种简化带来便利，但也可能有较高的风险，尤其当两表中存在同名但含义不同的列时，可能会导致意外的连接结果。因此，使用自然连接时应确保同名列在语义上是一致的，以避免误连接。

  示例语法：

  SELECT 列名 
  FROM 表1 
  NATURAL JOIN 表2;
  

  • 适用场景：当两个表有相同名称的列，并且希望根据这些列自动进行匹配连接时使用。注意：需确保同名列的语义和数据类型一致，避免产生误连接。

6. 图示化对比

为了更好地说明内连接、左外连接、右外连接和全外连接的区别，以下图表展示了这些连接方式的包含关系：

                 全外连接
       ┌─────────────┬─────────────┐
       │ 左外连接    │ 右外连接     │
       └─────────────┴─────────────┘
                 内连接

在内连接中，只返回匹配的部分（中间重叠部分）；左外连接则返回左表全部数据，并显示右表未匹配项为 NULL；右外连接返回右表全部数据，左表未匹配项为 NULL；全外连接则返回两个表中的所有数据，显示未匹配项为 NULL。

7. 表格示例

以下为每种 SQL 表连接方式的示例示范，通过示例可以直观了解连接方式的不同输出效果。

假设我们有两个表 employees 和 departments，其结构如下：

表1：员工（employees）

表2：部门（departments）

7.1 内连接（INNER JOIN）

查询每位员工所在的部门名称（只显示有匹配的部门记录的员工）。

SELECT employees.name, departments.department_name 
FROM employees 
INNER JOIN departments 
ON employees.department_id = departments.id;

结果：

说明：Charlie 的 department_id 为 NULL，因此没有显示在结果中。

7.2 左外连接（LEFT JOIN）

查询所有员工和他们的部门名称，即使没有匹配的部门记录。

SELECT employees.name, departments.department_name 
FROM employees 
LEFT JOIN departments 
ON employees.department_id = departments.id;

结果：

说明：Charlie 没有匹配的部门，因此 department_name 显示为 NULL。

7.3 右外连接（RIGHT JOIN）

查询所有部门以及对应的员工，即使某个部门中没有员工。

SELECT employees.name, departments.department_name 
FROM employees 
RIGHT JOIN departments 
ON employees.department_id = departments.id;

结果：

说明：Marketing 部门没有员工，因此 name 显示为 NULL。

7.4 全外连接（FULL JOIN）

查询所有员工和部门信息，返回未匹配项（对于不支持 FULL JOIN 的数据库，使用 UNION 替代）。

SELECT employees.name, departments.department_name 
FROM employees 
FULL JOIN departments 
ON employees.department_id = departments.id;

结果：

说明：Charlie 和 Marketing 均显示出来，尽管没有对应的匹配项。

7.5 交叉连接（CROSS JOIN）

生成员工和部门的所有组合（笛卡尔积）。

SELECT employees.name, departments.department_name 
FROM employees 
CROSS JOIN departments;

结果：

说明：所有员工和所有部门的组合都生成了 16 条记录（4 个员工 × 4 个部门）。

7.6 自连接（SELF JOIN）

查询 employees 表中员工及其直接上级（假设每个员工有一个 manager_id 列）。

假设 employees 表新增了一个 manager_id 列，数据如下：

表1：员工（employees）

查询每位员工和他们的经理：

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.id;

结果：

说明：Alice 是顶层，没有经理，所以 manager 显示为 NULL。

7.7 自然连接（NATURAL JOIN）

自然连接会自动匹配同名列（假设 employees 和 departments 表中 department_id 列同名）。

SELECT name, department_name 
FROM employees 
NATURAL JOIN departments;

结果：

说明：Charlie 没有 department_id 值，因而没有出现在结果中。