MySQL连接类型详解：内连接、外连接与自连接

MySQL连接类型详解：内连接、外连接与自连接

引用

CSDN

1.

https://blog.csdn.net/kaoyanjiayou0429/article/details/140615154

在数据库查询中，连接（JOIN）操作是数据检索的核心技术之一。MySQL支持多种连接类型，包括内连接、外连接和自连接。本文将详细介绍这三种连接类型的基本概念、使用场景，并通过一个实际案例帮助读者理解它们的具体应用。

内连接（INNER JOIN）

• 定义: 只返回两个表中匹配的记录。
• 用法: 当需要从两个表中提取匹配的数据时使用。
• SQL 示例:

SELECT columns FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

外连接（OUTER JOIN）

• 定义: 返回两个表中匹配的记录以及一个表中的所有记录（即使在另一个表中没有匹配项）。外连接分为左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）和全外连接（FULL OUTER JOIN）。
• 左外连接（LEFT JOIN）: 返回左表的所有记录，以及右表中匹配的记录。
• 右外连接（RIGHT JOIN）: 返回右表的所有记录，以及左表中匹配的记录。
• SQL 示例:

-- 左外连接
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_field = table2.common_field;

-- 右外连接
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_field = table2.common_field;

自连接（SELF JOIN）

• 定义: 一个表与其自身连接，通常用于查找表中与其他记录相关的数据。
• 用法: 当表中记录之间存在关系，需要比较同一表中的不同记录时使用。
• SQL 示例:

SELECT a.columns, b.columns
FROM table a
JOIN table b ON a.common_field = b.related_field;

案例示例

假设我们有两个表：employees和departments。employees表记录了员工的信息，而departments表记录了部门的信息。每个员工有一个部门ID，指向departments表。

表结构

-- 创建 departments 表
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL
);

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

插入数据

-- 插入部门数据
INSERT INTO departments (department_name) VALUES ('Sales'), ('Marketing'), ('HR'), ('IT'), ('Finance');

-- 插入员工数据
INSERT INTO employees (name, department_id) VALUES ('Alice', 1), ('Bob', 2), ('Charlie', 3), ('David', 1), ('Eva', 4),
('Frank', 2), ('Grace', 5), ('Hannah', 4), ('Ivy', NULL), ('Jack', 3);

1. 内连接（INNER JOIN）
   查询每个员工及其所在的部门名称。

SELECT e.name AS employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

2. 左外连接（LEFT JOIN）
   查询每个员工及其所在的部门名称，包括那些没有部门的员工。
   

SELECT e.name AS employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

3. 自连接（SELF JOIN）
   查询每个员工及其部门中其他员工的名字（例如，寻找与同部门员工相关的记录）。

SELECT e1.name AS employee_name, e2.name AS colleague_name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id != e2.employee_id;

总结

• 内连接: 提取匹配的记录。
• 外连接: 提取匹配的记录和一个表中的所有记录。
• 自连接: 用于比较同一表中的不同记录。