mysql学习教程，从入门到精通，SQL FULL JOIN 语句（25）

1、SQL FULL JOIN 语句

在SQL中，FULL JOIN（也被称为全外连接）是一种结合了两个表中的所有行的操作，包括两个表中匹配的行以及那些不匹配的行。如果在一个表中有行在另一个表中没有匹配，那么这些行将出现在结果集中，但是与之不匹配的列的值将是NULL。
下面是一个使用FULL JOIN语句的例子。假设我们有两个表：employees（员工表）和departments（部门表）。employees表包含员工的ID、姓名和所属部门ID，而departments表包含部门ID和部门名称。
employees 表结构示例：

departments 表结构示例：

如果我们想要查询所有员工及其所属部门，包括那些没有员工的部门（如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);

注意几个关键点：

1. FULL JOIN的使用：它确保了从两个表中返回所有行。如果employees表中的某行在departments表中没有匹配的department_id，则结果中该行的department_name将为NULL。反之亦然。
2. COALESCE函数：在ORDER BY子句中，我们使用了COALESCE函数来确保排序能够顺利进行，即使某些列是NULL。COALESCE返回其参数列表中的第一个非空表达式。这里，它确保了如果EmployeeName为NULL（意味着该行来自departments表且没有对应的员工），则使用DepartmentName进行排序。然而，在这个特定的例子中，由于我们想要根据员工名或部门名进行排序，而这个逻辑可能需要更复杂的逻辑来确保顺序的合理性（比如，先按部门名排序，再按员工名排序，但只针对有值的行），这里仅作为展示COALESCE函数的一个用法。
3. 实际排序需求：根据实际需求，你可能需要调整排序逻辑或移除ORDER BY子句，特别是如果你不需要特定的排序顺序。
4. 结果集：结果集将包括所有员工及其对应的部门名（如果存在），以及所有没有员工的部门名（如Finance）。对于没有对应部门的员工（在上面的示例中不存在），理论上也将包含，但在实际情况下，如果数据库设计得当，这种情况应该很少见。

当然可以，以下是一些具体的SQL FULL JOIN 案例，这些案例将帮助您更好地理解FULL JOIN的工作原理。

案例1：员工与部门

场景：
假设我们有两个表，employees（员工表）和departments（部门表）。我们需要列出所有员工及其所属部门，包括那些没有分配部门的员工和没有员工的部门。
employees 表

departments 表

SQL 查询

SELECT   
    employees.name AS EmployeeName,  
    departments.department_name AS DepartmentName  
FROM   
    employees  
FULL JOIN   
    departments ON employees.department_id = departments.department_id;

结果

这个查询返回了所有员工和部门，包括Carol（没有部门）和Finance（没有员工）。

案例2：订单与客户

场景：
我们有两个表，orders（订单表）和customers（客户表）。我们需要查看所有订单及其对应的客户，包括那些没有关联到任何客户的订单。
customers 表

orders 表

SQL 查询

SELECT   
    customers.name AS CustomerName,  
    orders.order_id,  
    orders.order_date  
FROM   
    customers  
FULL JOIN   
    orders ON customers.customer_id = orders.customer_id;

结果

这个查询返回了所有订单和客户，包括订单103（没有关联到任何客户）。

案例3：学生成绩

场景：
我们有两个表，students（学生表）和grades（成绩表）。我们需要列出所有学生的成绩，包括那些没有成绩的学生。
students 表

grades 表

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查询的结果将是：

这个查询返回了所有学生及其成绩，包括Carol（没有成绩）。