mysql学习教程，从入门到精通，SQL FULL JOIN 语句（25）
1、SQL FULL JOIN 语句
在SQL中，FULL JOIN（也被称为全外连接）是一种结合了两个表中的所有行的操作，包括两个表中匹配的行以及那些不匹配的行。如果在一个表中有行在另一个表中没有匹配，那么这些行将出现在结果集中，但是与之不匹配的列的值将是NULL。
下面是一个使用FULL JOIN语句的例子。假设我们有两个表：employees（员工表）和departments（部门表）。employees表包含员工的ID、姓名和所属部门ID，而departments表包含部门ID和部门名称。
employees 表结构示例：
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 101 |
| 2 | Bob | 102 |
| 3 | Carol | 103 |
departments 表结构示例：
| department_id | department_name |
|---|---|
| 101 | HR |
| 102 | IT |
| 104 | Finance |
如果我们想要查询所有员工及其所属部门，包括那些没有员工的部门（如Finance），以及那些不属于任何已知部门（在我们的示例中不存在，但理论上可能）的员工，我们可以使用FULL JOIN。
SQL查询语句如下：
SELECT employees.name AS EmployeeName,departments.department_name AS DepartmentName
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id
ORDER BY COALESCE(employees.name, departments.department_name);
注意几个关键点：
FULL JOIN的使用：它确保了从两个表中返回所有行。如果employees表中的某行在departments表中没有匹配的department_id，则结果中该行的department_name将为NULL。反之亦然。COALESCE函数：在ORDER BY子句中，我们使用了COALESCE函数来确保排序能够顺利进行，即使某些列是NULL。COALESCE返回其参数列表中的第一个非空表达式。这里，它确保了如果EmployeeName为NULL（意味着该行来自departments表且没有对应的员工），则使用DepartmentName进行排序。然而，在这个特定的例子中，由于我们想要根据员工名或部门名进行排序，而这个逻辑可能需要更复杂的逻辑来确保顺序的合理性（比如，先按部门名排序，再按员工名排序，但只针对有值的行），这里仅作为展示COALESCE函数的一个用法。- 实际排序需求：根据实际需求，你可能需要调整排序逻辑或移除
ORDER BY子句，特别是如果你不需要特定的排序顺序。 - 结果集：结果集将包括所有员工及其对应的部门名（如果存在），以及所有没有员工的部门名（如Finance）。对于没有对应部门的员工（在上面的示例中不存在），理论上也将包含，但在实际情况下，如果数据库设计得当，这种情况应该很少见。
当然可以，以下是一些具体的SQL FULL JOIN 案例，这些案例将帮助您更好地理解FULL JOIN的工作原理。
案例1：员工与部门
场景：
假设我们有两个表，employees（员工表）和departments（部门表）。我们需要列出所有员工及其所属部门，包括那些没有分配部门的员工和没有员工的部门。
employees 表
| employee_id | name | department_id |
|---|---|---|
| 1 | Alice | 101 |
| 2 | Bob | 102 |
| 3 | Carol | NULL |
departments 表
| department_id | department_name |
|---|---|
| 101 | HR |
| 102 | IT |
| 103 | Finance |
SQL 查询
SELECT employees.name AS EmployeeName, departments.department_name AS DepartmentName
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;
结果
| EmployeeName | DepartmentName |
|---|---|
| Alice | HR |
| Bob | IT |
| Carol | NULL |
| NULL | Finance |
这个查询返回了所有员工和部门，包括Carol（没有部门）和Finance（没有员工）。
案例2：订单与客户
场景：
我们有两个表，orders（订单表）和customers（客户表）。我们需要查看所有订单及其对应的客户，包括那些没有关联到任何客户的订单。
customers 表
| customer_id | name |
|---|---|
| 1 | John |
| 2 | Jane |
orders 表
| order_id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2023-01-01 |
| 102 | 2 | 2023-01-02 |
| 103 | NULL | 2023-01-03 |
SQL 查询
SELECT customers.name AS CustomerName, orders.order_id, orders.order_date
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;
结果
| CustomerName | order_id | order_date |
|---|---|---|
| John | 101 | 2023-01-01 |
| Jane | 102 | 2023-01-02 |
| NULL | 103 | 2023-01-03 |
这个查询返回了所有订单和客户，包括订单103（没有关联到任何客户）。
案例3：学生成绩
场景：
我们有两个表，students（学生表）和grades（成绩表）。我们需要列出所有学生的成绩，包括那些没有成绩的学生。
students 表
| student_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
grades 表
| grade_id | student_id | score |
|---|---|---|
| 1 | 1 | 90 |
| 2 | 2 | 85 |
SQL 查询
SELECT students.name AS StudentName, grades.score
FROM students
FULL JOIN grades ON students.student_id = grades.student_id;
注意：在实际应用中，由于FULL JOIN可能会产生重复的行（尽管在这个特定案例中不会），并且MySQL等某些数据库不支持FULL JOIN，我们通常会使用LEFT JOIN结合UNION或COALESCE等函数来达到类似的效果。但为了直接展示FULL JOIN的用途，这里假设我们使用的数据库支持FULL JOIN。
然而，为了兼容性，我们可以使用LEFT JOIN和UNION来模拟FULL JOIN的效果：
SELECT students.name AS StudentName, grades.score
FROM students
LEFT JOIN grades ON students.student_id = grades.student_id
UNION
SELECT students.name, NULL AS score
FROM students
RIGHT JOIN grades ON students.student_id = grades.student_id
WHERE students.student_id IS NULL;
但请注意，上面的RIGHT JOIN部分实际上是多余的，因为LEFT JOIN已经覆盖了所有情况。正确的模拟应该是只使用LEFT JOIN加上对grades表中未匹配记录的单独查询（如果有的话，但在这个案例中不需要），但这里为了展示目的而包含了它。
对于支持FULL JOIN的数据库，原始FULL JOIN查询的结果将是：
| StudentName | score |
|---|---|
| Alice | 90 |
| Bob | 85 |
| Carol | NULL |
这个查询返回了所有学生及其成绩，包括Carol（没有成绩）。