Mysql-连接类型总结

在 MySQL 中，连接（JOIN）操作用于从两个或多个表中检索相关数据。主要的连接类型包括内连接（INNER JOIN）、外连接（OUTER JOIN）和自连接（SELF JOIN）。下面是对这三种连接的总结和一个示例案例。

内连接（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 示例:

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;

1. 自连接（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;

总结

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