MySQL 多表连接查询

多表连接用于从多个表中查询关联数据，是最重要的查询技术之一。

连接类型

示例数据

-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO departments VALUES
(1, '技术部'),
(2, '销售部'),
(3, '人事部');

-- 员工表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, '张三', 1, 8000),
(2, '李四', 2, 7000),
(3, '王五', 1, 9000),
(4, '赵六', NULL, 6000);

INNER JOIN 内连接

-- 查询员工及其部门
SELECT e.name, e.salary, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 结果: 张三-技术部, 李四-销售部, 王五-技术部
-- 赵六没有部门，不显示

LEFT JOIN 左连接

-- 返回左表所有记录，右表无匹配则为NULL
SELECT e.name, e.salary, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- 结果: 张三-技术部, 李四-销售部, 王五-技术部, 赵六-NULL

RIGHT JOIN 右连接

-- 返回右表所有记录，左表无匹配则为NULL
SELECT e.name, e.salary, d.name AS dept
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- 结果: 张三-技术部, 李四-销售部, 王五-技术部, NULL-人事部

多表连接

-- 创建薪资等级表
CREATE TABLE salary_grades (
    grade VARCHAR(10),
    min_salary DECIMAL(10, 2),
    max_salary DECIMAL(10, 2)
);

INSERT INTO salary_grades VALUES
('A', 5000, 7000),
('B', 7000, 9000),
('C', 9000, 12000);

-- 三表连接
SELECT e.name, d.name AS dept, sg.grade
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
LEFT JOIN salary_grades sg ON e.salary >= sg.min_salary AND e.salary < sg.max_salary;

自连接

-- 员工与经理在同一张表
CREATE TABLE staff (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO staff VALUES
(1, '张总', NULL),
(2, '李经理', 1),
(3, '王员工', 2);

-- 自连接查询员工和经理
SELECT e.name AS 员工, m.name AS 经理
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.id;

USING 简化连接

-- 当连接列名相同时可用 USING
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d USING (id);  -- 两表都有 id 列时

优先使用 INNER JOIN 或 LEFT JOIN，避免 RIGHT JOIN 以提高可读性。

要点总结

• INNER JOIN 只返回匹配记录
• LEFT JOIN 返回左表全部，右表无匹配为 NULL
• 多表连接用多个 JOIN 子句
• 自连接用于层级数据查询