【Java 进阶篇】MySQL多表查询之外连接详解

野性酷女 2023-10-16 16:31 93阅读 0赞

![在这里插入图片描述][72872c6185d14f17ac71b6ec22e5e093.png]

在MySQL数据库中，多表查询是一种常见且强大的功能，允许您在多个表之间执行联接操作，从而检索、过滤和组合数据。在本篇博客中，我们将深入探讨多表查询的一种类型，即外连接（Outer Join），并详细介绍其语法、用途和示例。无论您是刚开始学习数据库还是想深入了解MySQL的查询功能，本文都将为您提供有价值的信息。

### 1. 外连接概述 ###

外连接是一种多表查询，它返回两个表中满足连接条件的所有行，以及左表中未找到匹配行的右表的所有行（左外连接），或右表中未找到匹配行的左表的所有行（右外连接）。外连接通常用于查找相关联的数据，即使在某些表中没有匹配的行。

外连接有三种类型：

*  左外连接（LEFT JOIN或LEFT OUTER JOIN）：返回左表中的所有行和右表中与左表匹配的行。如果右表中没有匹配的行，则返回NULL值。
 *  右外连接（RIGHT JOIN或RIGHT OUTER JOIN）：与左外连接相反，返回右表中的所有行和左表中与右表匹配的行。如果左表中没有匹配的行，则返回NULL值。
 *  全外连接（FULL JOIN或FULL OUTER JOIN）：返回左表和右表中的所有行，并且在没有匹配的行时返回NULL值。全外连接在某些数据库中可能不支持。

### 2. 外连接语法 ###

外连接的语法基本上由以下部分组成：

SELECT 列1, 列2, ...
    FROM 表1
    LEFT JOIN 表2
    ON 表1.列 = 表2.列;

其中，`SELECT`语句用于选择要检索的列，`FROM`语句指定要连接的表，`LEFT JOIN`表示进行左外连接，`ON`语句指定连接条件。

以下是一个更具体的示例：

SELECT customers.customer_id, orders.order_id
    FROM customers
    LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

在上述示例中，我们选择了`customers`表中的`customer_id`列和`orders`表中的`order_id`列，然后进行了左外连接，连接条件是`customer_id`。

### 3. 外连接示例 ###

#### 3.1 左外连接示例 ####

让我们通过一个示例来说明左外连接的用法。假设我们有两个表：`students`和`scores`，用于跟踪学生的成绩信息。`students`表包含学生的基本信息，`scores`表包含学生的成绩信息。我们想要列出所有学生以及他们的成绩（如果有的话），即使有些学生还没有成绩记录。

-- 创建示例表 students 和 scores
    CREATE TABLE students (
        student_id INT PRIMARY KEY,
        student_name VARCHAR(255)
    );
    
    CREATE TABLE scores (
        student_id INT,
        subject VARCHAR(255),
        score INT
    );
    
    -- 插入示例数据
    INSERT INTO students (student_id, student_name)
    VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
    
    INSERT INTO scores (student_id, subject, score)
    VALUES (1, 'Math', 95), (2, 'Math', 88);

现在，我们可以使用左外连接来检索所有学生以及他们的成绩：

SELECT students.student_name, scores.subject, scores.score
    FROM students
    LEFT JOIN scores
    ON students.student_id = scores.student_id;

查询结果将包括所有学生的信息，即使有些学生没有成绩记录。例如，Charlie没有成绩记录，但仍然在结果中显示：

#### 3.2 右外连接示例 ####

右外连接的示例与左外连接类似，只是它返回右表中的所有行，即使在左表中没有匹的行。假设我们有两个表：`orders`和`customers`，用于跟踪客户和他们的订单信息。`orders`表包含订单的详细信息，`customers`表包含客户的基本信息。我们想要列出所有的订单以及订单所属的客户（即使有些订单没有匹配的客户信息）。

-- 创建示例表 orders 和 customers
    CREATE TABLE customers (
        customer_id INT PRIMARY KEY,
        customer_name VARCHAR(255)
    );
    
    CREATE TABLE orders (
        order_id INT PRIMARY KEY,
        order_date DATE,
        customer_id INT,
        total_amount DECIMAL(10, 2)
    );
    
    -- 插入示例数据
    INSERT INTO customers (customer_id, customer_name)
    VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
    
    INSERT INTO orders (order_id, order_date, customer_id, total_amount)
    VALUES (101, '2023-01-15', 1, 100.00), (102, '2023-01-20', 2, 150.00);

现在，我们可以使用右外连接来检索所有订单以及订单所属的客户信息：

SELECT customers.customer_name, orders.order_id, orders.order_date, orders.total_amount
    FROM customers
    RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

查询结果将包括所有订单的信息，即使有些订单没有匹配的客户信息。例如，订单编号102没有与客户信息匹配，但仍然在结果中显示：

+-------------+---------+------------+---------------+
    | customer_name| order_id| order_date | total_amount  |
    +-------------+---------+------------+---------------+
    | Alice       | 101     | 2023-01-15 | 100.00        |
    | Bob         | 102     | 2023-01-20 | 150.00        |
    | Charlie     | NULL    | NULL       | NULL          |
    +-------------+---------+------------+---------------+

#### 3.3 全外连接示例 ####

全外连接返回左表和右表中的所有行，无论是否有匹配的行。让我们以一个简单的示例来说明全外连接。假设我们有两个表：`employees`和`departments`，用于跟踪员工和他们所属的部门信息。`employees`表包含员工的详细信息，`departments`表包含部门的基本信息。我们想要列出所有员工以及他们所属的部门，即使有些员工没有分配到部门，以及有些部门没有员工。

-- 创建示例表 employees 和 departments
    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(255)
    );
    
    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(255),
        department_id INT
    );
    
    -- 插入示例数据
    INSERT INTO departments (department_id, department_name)
    VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');
    
    INSERT INTO employees (employee_id, employee_name, department_id)
    VALUES (101, 'Alice', 1), (102, 'Bob', 2);

现在，我们可以使用全外连接来检索所有员工以及他们所属的部门信息：

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

查询结果将包括所有员工和所有部门的信息，即使有些员工没有分配到部门，以及有些部门没有员工。例如，员工Bob没有分配到部门，部门Finance没有员工，但它们仍然在结果中显示：

+---------------+------------------+
    | employee_name | department_name  |
    +---------------+------------------+
    | Alice         | HR               |
    | Bob           | IT               |
    | NULL          | Finance          |
    +---------------+------------------+

### 4. 外连接的应用场景 ###

外连接在实际应用中有许多用途，其中一些常见的包括：

*  列出所有的父级和子级数据，即使某些父级没有子级，或某些子级没有父级。
 *  查找所有具有或不具有某种属性的数据，以便进行数据清理或分析。
 *  分析用户行为，以查找哪些用户执行了某些操作，哪些用户没有执行。
 *  联接不同类型的数据，例如在一张表中存储产品信息，而在另一张表中存储评论信息，然后使用外连接查找哪些产品没有评论或哪些评论没有对应的产品。
 *  在数据仓库或报表生成中，使用外连接来组合多个维度和事实表，以便生成全面的报表。

### 5. 注意事项 ###

在使用外连接时，需要注意以下几点：

*  性能问题：外连接可能会导致性能问题，特别是在处理大型数据集时。要谨慎使用外连接，确保数据库的索引和查询优化得当。
 *  结果集大小：外连接的结果集可能会非常大，特别是在全外连接的情况下。请确保您有足够的内存和计算资源来处理结果集。
 *  空值处理：外连接的结果中可能包含NULL值，因此在使用结果集时需要谨慎处理NULL值。
 *  查询复杂性：外连接可以创建复杂的查询，难以理解和维护。请使用注释和良好的命名来提高代码的可读性。

### 6. 总结 ###

外连接是一种有用的多表查询工具，允许您检索两个表之间的相关数据，即使没有匹配的行也可以返回结果。左外连接、右外连接和全外连接分别用于不同的场景，帮助您分析和处理数据。

在使用外连接时，请确保考虑性能、结果集大小、空值处理和查询复杂性等因素，以便充分利用这一功能。外连接在数据分析、报表生成和数据清理等方面具有广泛的应用。

希望本文对您理解MySQL外连接以及如何使用它们有所帮助。如果您想要深入学习多表查询和其他数据库主题，请继续探索更多相关资源和教程。

<table> 
 <tbody> 
  <tr> 
   <td> <strong><font>作者信息</font></strong><br> <br> <font> 作者 ： 繁依Fanyi</font><br> <font>CSDN：</font> <a href="https://blog.csdn.net/qq_21484461?spm=1000.2115.3001.5343" rel="nofollow"><font>https://techfanyi.blog.csdn.net</font></a><br> <font>掘金：</font><a href="https://juejin.cn/user/4154386571867191" rel="nofollow"><font>https://juejin.cn/user/4154386571867191</font></a> </td> 
  </tr> 
 </tbody> 
</table>

[72872c6185d14f17ac71b6ec22e5e093.png]: https://img-blog.csdnimg.cn/72872c6185d14f17ac71b6ec22e5e093.png