MySQL(19)什么是联合查询（JOIN）？

联合查询（JOIN）是一种关系数据库操作，用于在结果集中组合来自两个或多个表的行。JOIN操作允许我们通过定义表之间的关系来查询和整合分散在多个表中的数据。JOIN 的类型包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN 等。

主要的 JOIN 类型

1. INNER JOIN：返回两个表中匹配的记录。
2. LEFT JOIN (LEFT OUTER JOIN)：返回左表中的所有记录，即使右表中没有匹配的记录。
3. RIGHT JOIN (RIGHT OUTER JOIN)：返回右表中的所有记录，即使左表中没有匹配的记录。
4. FULL OUTER JOIN：返回左表和右表中的所有记录，其中没有匹配的部分显示为NULL。（注意：MySQL不直接支持FULL OUTER JOIN）

基本 JOIN 语法

SELECT columns
FROM table1
{INNER | LEFT | RIGHT | FULL OUTER} JOIN table2
ON table1.column = table2.column;

示例数据库和表结构

假设我们有一个简单的公司数据库，其中有两个表：employees 和 departments。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建 employees 表
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE,
    dept_id INT
);

-- 创建 departments 表
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- 插入初始数据
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date, dept_id)
VALUES 
('John Doe', 'Manager', 75000.00, '2023-10-01', 1),
('Jane Smith', 'Developer', 60000.00, '2023-09-01', 2);

INSERT INTO departments (dept_name) 
VALUES 
('HR'),
('Engineering');

INNER JOIN 示例

INNER JOIN 仅返回两个表中匹配的记录。

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
INNER JOIN 
    departments d ON e.dept_id = d.dept_id;

结果：

emp_id | emp_name   | emp_position | emp_salary | hire_date  | dept_name
-------|------------|--------------|------------|------------|-----------
1      | John Doe   | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith | Developer    | 60000.00   | 2023-09-01 | Engineering

LEFT JOIN 示例

LEFT JOIN 返回左表中的所有记录，即使右表中没有匹配的记录。

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
LEFT JOIN 
    departments d ON e.dept_id = d.dept_id;

结果：

emp_id | emp_name   | emp_position | emp_salary | hire_date  | dept_name
-------|------------|--------------|------------|------------|-----------
1      | John Doe   | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith | Developer    | 60000.00   | 2023-09-01 | Engineering

RIGHT JOIN 示例

RIGHT JOIN 返回右表中的所有记录，即使左表中没有匹配的记录。

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.dept_id = d.dept_id;

结果：

emp_id | emp_name   | emp_position | emp_salary | hire_date  | dept_name
-------|------------|--------------|------------|------------|-----------
1      | John Doe   | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith | Developer    | 60000.00   | 2023-09-01 | Engineering

FULL OUTER JOIN 示例

MySQL不直接支持 FULL OUTER JOIN，不过可以通过 UNION ALL 和 LEFT JOIN / RIGHT JOIN 组合来模拟 FULL OUTER JOIN。

SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
LEFT JOIN 
    departments d ON e.dept_id = d.dept_id
UNION ALL
SELECT 
    e.emp_id,
    e.emp_name,
    e.emp_position,
    e.emp_salary,
    e.hire_date,
    d.dept_name
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

结果：

emp_id | emp_name   | emp_position | emp_salary | hire_date  | dept_name
-------|------------|--------------|------------|------------|-----------
1      | John Doe   | Manager      | 75000.00   | 2023-10-01 | HR
2      | Jane Smith | Developer    | 60000.00   | 2023-09-01 | Engineering
NULL   | NULL       | NULL         | NULL       | NULL       | Sales

小结

通过以上代码示例，展示了如何在 MySQL 中使用各种类型的 JOIN 操作来整合来自多个表的数据。JOIN 是关系数据库中最常用的操作之一，能够帮助我们从分散在多个表中的数据中获取所需的信息。