数据库联合查询（JOIN）详解：历史最全教程

在关系型数据库中，联合查询（JOIN）是将来自两个或多个表的数据按一定的条件组合在一起的操作。联合查询通常用于检索不同表之间的关联数据，是 SQL 查询中的核心功能之一。

一、联合查询的基本概念

联合查询，顾名思义，就是将多个表中的数据组合在一起。通常来说，联合查询的目的是通过某种关联条件，将多个表中的相关数据行按照特定方式进行连接。最常见的联合查询基于表与表之间的“主外键关系”或“相等条件”进行连接。

联合查询的核心思想是：你可以从不同的表中提取数据，而不仅仅局限于从单个表中查询，最终将它们“联合”起来，形成符合需求的结果集。

联合查询常见类型：

1. 内连接（INNER JOIN）
2. 左连接（LEFT JOIN 或 LEFT OUTER JOIN）
3. 右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）
4. 全连接（FULL JOIN 或 FULL OUTER JOIN）
5. 交叉连接（CROSS JOIN）

二、SQL 联合查询的类型详解

1. 内连接（INNER JOIN）

定义：
内连接是最常见的连接类型，它返回两个表中匹配的行。当某一方的行没有匹配时，这些行就不会出现在结果集内。

示例：假设有两个表：employees
和departments
，分别包含员工和部门信息。

• employees
  （员工表）：

  emp_id	emp_name	dept_id
  1	Alice	10
  2	Bob	20
  3	Charlie	10
  4	David	NULL

• departments
  （部门表）：

  dept_id	dept_name
  10	HR
  20	IT

如果你要查询员工和他们所属部门的名称，可以通过INNER JOIN
进行连接：

SELECT 
  employees.emp_name, 
  departments.dept_name
FROM employees
INNER JOIN departments 
ON employees.dept_id=departments.dept_id;

输出结果：

在此查询中，只有那些有匹配部门的员工会被返回。没有匹配的行（例如 David）会被过滤掉。

2. 左连接（LEFT JOIN LEFT OUTER JOIN）

定义：
左连接返回左表中的所有行，即使右表中没有匹配的行。如果右表没有匹配，结果中会包含 NULL。

示例：继续使用employees
和departments
表，如果我们要查询所有员工及其部门，如果没有部门也要显示员工的名字：

SELECT 
  employees.emp_name, 
  departments.dept_name
FROM employees
LEFT JOIN departments 
ON employees.dept_id= departments.dept_id;

输出结果：

在此查询中，左表employees
中的所有行都被返回，即使右表departments
没有匹配的行。对于没有部门的员工（如 David），部门列将显示为NULL
。

3. 右连接（RIGHT JOIN RIGHT OUTER JOIN）

定义：
右连接与左连接相似，只是它返回右表中的所有行，即使左表中没有匹配的行。如果左表没有匹配，结果中会包含 NULL。

示例：查询所有部门和属于这些部门的员工，即使某些部门没有员工：

SELECT 
  employees.emp_name, 
  departments.dept_name
FROM employees
RIGHT JOIN departments
ON employees.dept_id = departments.dept_id;

输出结果：

在此查询中，所有部门都会被列出，即使它们没有员工。对于没有员工的部门（如 Marketing），emp_name
将显示为NULL
。

4. 全连接（FULL JOIN FULL OUTER JOIN）

定义：
全连接返回左表和右表中的所有行。如果某一表没有匹配的行，则该表的列显示为NULL
。

示例：查询所有员工和所有部门，无论是否有匹配的记录：

SELECT 
  employees.emp_name, 
  departments.dept_name
FROM employees
FULL JOIN departments
ON employees.dept_id= departments.dept_id;

输出结果：

在此查询中，所有员工和部门都被列出。如果某一边没有匹配的记录，则结果中对应的列会显示NULL
。

5. 交叉连接（CROSS JOIN）

定义：
交叉连接会返回两个表的笛卡尔积，即左表的每一行都会与右表的每一行匹配，返回的结果行数是左表行数乘以右表行数。

示例：假设有一个员工表employees
和一个技能表skills
，我们要查询每个员工和所有技能的组合：

SELECT 
  employees.emp_name, 
  skills.skill_name
FROM employees
CROSS JOIN skills;

输出结果：

在此查询中，每个员工与每个技能都进行了组合，返回了所有可能的组合。

三、使用联合查询时的注意事项

1. ON vs WHERE：

   • ON
     
     用于指定联接条件（如两表之间的关联字段）。
   • WHERE
     
     用于对查询结果进一步筛选，可以和ON
     配合使用，特别是在某些类型的连接中，WHERE
     也可以作为过滤条件。

2. 避免重复数据：

   • SELECT DISTINCT employees.emp_nameFROM employees INNER JOIN departments ON employees.dept_id=departments.dept_id;
     

     如果连接的表中包含重复的字段，返回的结果可能会包含重复的行。可以使用DISTINCT
     来避免重复行。

3. 连接顺序：

   • 在 SQL 中，连接的顺序可能会影响查询的性能，尤其是当表的数据量非常大时。可以通过优化查询计划来提高查询效率。

4. 连接多个表：

   你可以通过多个JOIN操作连接多个表。例如：
   SELECT employees.emp_name, departments.dept_name, projects.project_name FROM employees INNER JOIN departments ON employees.dept_id= departments.dept_id INNER JOIN projects ON employees.emp_id= projects.emp_id;
   这将返回员工、部门和项目的关联数据。
   
   
   

5. NULL 值处理：

   在联合查询时，特别是在LEFT JOIN和RIGHT JOIN中，NULL 值可能会出现，表示某些数据未能匹配。处理 NULL 值时需要特别注意，比如使用COALESCE()函数填充默认值。
   SELECT employees.emp_name,COALESCE(departments.dept_name,'No Department')AS dept_nameFROM employees LEFT JOIN departments ON employees.dept_id = departments.dept_id;
   
   

四、总结

联合查询（JOIN）是 SQL 中用于在多个表之间建立联系并获取相关数据的强大工具。它能够根据不同的需求选择合适的连接类型：内连接、左连接、右连接、全连接或交叉连接。每种连接方式有不同的用途，合理使用它们可以大大提高查询效率和结果的准确性。

通过掌握不同类型的连接方式及其应用，你可以更灵活地处理复杂的数据查询需求，尤其是在处理大型数据库和复杂数据模型时，JOIN 是必不可少的查询工具。

希望这篇教程能帮助你深入理解 SQL 联合查询，并在实际工作中灵活运用！