多表联合查询（Join）是SQL中一种用来在多个表之间建立关联并获取相关数据的操作。常见的联合查询类型包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。下面详细介绍每种联合查询的使用方法，并结合代码示例。
示例数据库和表结构
假设我们有一个公司数据库，其中有两个表：employees 和 departments。
-- 创建数据库
CREATE DATABASE company;
-- 选择数据库
USE company;
-- 创建表 departments
CREATE TABLE departments (
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL
);
-- 创建表 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,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
-- 插入示例数据到 departments 表
INSERT INTO departments (dept_name)
VALUES
('HR'),
('Engineering'),
('Marketing');
-- 插入示例数据到 employees 表
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),
('Alice Johnson', 'Analyst', 50000.00, '2023-08-01', 1),
('Bob Brown', 'Developer', 70000.00, '2023-07-01', 2),
('Charlie Brown', 'Tester', 55000.00, '2023-11-01', 3),
('Emma White', 'Developer', 60000.00, '2023-08-20', 2);
1. 内连接（INNER JOIN）
INNER JOIN 返回两个表中满足连接条件的记录。
示例：获取每个员工及其所在部门的名称
SELECT employees.emp_name, employees.emp_position, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
结果：
emp_name | emp_position | dept_name
--------------|--------------|-----------
John Doe | Manager | HR
Jane Smith | Developer | Engineering
Alice Johnson | Analyst | HR
Bob Brown | Developer | Engineering
Charlie Brown | Tester | Marketing
Emma White | Developer | Engineering
2. 左连接（LEFT JOIN）
LEFT JOIN 返回左表中的所有记录，以及右表中满足连接条件的记录。不满足条件的右表记录将显示为 NULL。
示例：获取每个员工及其所在部门的名称（包括没有部门的员工）
假设我们插入一个没有部门的员工：
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date)
VALUES ('Mark Twain', 'Consultant', 80000.00, '2023-12-01');
然后执行左连接查询：
SELECT employees.emp_name, employees.emp_position, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;
结果：
emp_name | emp_position | dept_name
--------------|--------------|-----------
John Doe | Manager | HR
Jane Smith | Developer | Engineering
Alice Johnson | Analyst | HR
Bob Brown | Developer | Engineering
Charlie Brown | Tester | Marketing
Emma White | Developer | Engineering
Mark Twain | Consultant | NULL
3. 右连接（RIGHT JOIN）
RIGHT JOIN 返回右表中的所有记录，以及左表中满足连接条件的记录。不满足条件的左表记录将显示为 NULL。
示例：获取每个员工及其所在部门的名称（包括没有员工的部门）
SELECT employees.emp_name, employees.emp_position, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
结果：
emp_name | emp_position | dept_name
--------------|--------------|-----------
John Doe | Manager | HR
Alice Johnson | Analyst | HR
Jane Smith | Developer | Engineering
Bob Brown | Developer | Engineering
Emma White | Developer | Engineering
Charlie Brown | Tester | Marketing
NULL | NULL | HR
4. 全连接（FULL JOIN）
FULL JOIN 返回两个表中的所有记录，只要在其中至少有一个表中有匹配。MySQL 不直接支持 FULL JOIN，但可以通过使用 UNION 来实现。
示例：获取所有员工及其所在部门的名称（包括没有部门的员工和没有员工的部门）
SELECT employees.emp_name, employees.emp_position, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id
UNION
SELECT employees.emp_name, employees.emp_position, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
结果：
emp_name | emp_position | dept_name
--------------|--------------|-----------
John Doe | Manager | HR
Alice Johnson | Analyst | HR
Jane Smith | Developer | Engineering
Bob Brown | Developer | Engineering
Emma White | Developer | Engineering
Charlie Brown | Tester | Marketing
Mark Twain | Consultant | NULL
NULL | NULL | HR
5. 交叉连接（CROSS JOIN）
CROSS JOIN 返回两个表的笛卡尔积，即所有可能的组合。
示例：获取所有员工与所有部门的组合
SELECT employees.emp_name, departments.dept_name
FROM employees
CROSS JOIN departments;
结果：
emp_name | dept_name
--------------|-----------
John Doe | HR
John Doe | Engineering
John Doe | Marketing
Jane Smith | HR
Jane Smith | Engineering
Jane Smith | Marketing
Alice Johnson | HR
Alice Johnson | Engineering
Alice Johnson | Marketing
Bob Brown | HR
Bob Brown | Engineering
Bob Brown | Marketing
Charlie Brown | HR
Charlie Brown | Engineering
Charlie Brown | Marketing
Emma White | HR
Emma White | Engineering
Emma White | Marketing
Mark Twain | HR
Mark Twain | Engineering
Mark Twain | Marketing
6. 自连接（SELF JOIN）
自连接是表与其自身进行连接。
示例：获取每个员工及其直属上司的姓名
假设 employees 表中有一个 manager_id 列表示员工的直属上司：
-- 添加 manager_id 列
ALTER TABLE employees ADD manager_id INT;
-- 更新 manager_id 值
UPDATE employees SET manager_id = 1 WHERE emp_id = 2 OR emp_id = 4 OR emp_id = 6;
UPDATE employees SET manager_id = 2 WHERE emp_id = 3;
UPDATE employees SET manager_id = NULL WHERE emp_id = 1;
然后执行自连接查询：
SELECT e1.emp_name AS employee, e2.emp_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;
结果：
employee | manager
--------------|--------
John Doe | NULL
Jane Smith | John Doe
Alice Johnson | Jane Smith
Bob Brown | John Doe
Charlie Brown | NULL
Emma White | John Doe
Mark Twain | NULL
小结
多表联合查询是SQL中非常强大的工具，它允许您在多个表之间建立关联并获取相关数据。通过使用 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、CROSS JOIN 和自连接，您可以实现各种复杂的数据查询和分析。上述示例展示了如何在不同情况下使用这些联合查询，以实现多表数据的关联和提取。
