Muliminty Note

04-高级查询技巧

9分钟阅读

高级查询技巧

本节介绍 SQL 中的 JOIN 连接查询、子查询和集合操作。

准备测试数据

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    manager_id INT
);
 
-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    manager_id INT
);
 
-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    amount DECIMAL(10, 2),
    order_date DATE
);
 
-- 插入测试数据
INSERT INTO departments (name, manager_id) VALUES
('技术部', 1),
('市场部', 3),
('人事部', 5),
('财务部', NULL);
 
INSERT INTO employees (name, department_id, salary, manager_id) VALUES
('张三', 1, 20000, NULL),
('李四', 1, 15000, 1),
('王五', 2, 18000, NULL),
('赵六', 2, 12000, 3),
('钱七', 3, 10000, NULL),
('孙八', 1, 16000, 1),
('周九', NULL, 11000, NULL);
 
INSERT INTO orders (employee_id, amount, order_date) VALUES
(2, 5000, '2024-01-15'),
(2, 8000, '2024-01-20'),
(4, 3000, '2024-02-10'),
(4, 6000, '2024-02-15'),
(6, 10000, '2024-03-01');

JOIN 连接查询

JOIN 用于从多个表中获取关联数据。

INNER JOIN 内连接

返回两表中匹配的行。

-- 查询员工及其部门信息
SELECT 
    e.name AS employee_name,
    e.salary,
    d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
 
-- 简写形式
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id;

结果：只返回有部门的员工（周九没有部门，不会出现）

LEFT JOIN 左连接

返回左表所有行，右表匹配不上则为 NULL。

-- 查询所有员工及其部门（包括没有部门的员工）
SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

结果：周九也会出现，department_name 为 NULL

RIGHT JOIN 右连接

返回右表所有行，左表匹配不上则为 NULL。

-- 查询所有部门及其员工（包括没有员工的部门）
SELECT 
    d.name AS department_name,
    e.name AS employee_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

结果：财务部也会出现，employee_name 为 NULL

FULL OUTER JOIN 全外连接

返回两表所有行（MySQL 不直接支持，需用 UNION 模拟）。

-- PostgreSQL 直接支持
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
 
-- MySQL 使用 UNION 模拟
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

CROSS JOIN 交叉连接

返回两表的笛卡尔积（所有组合）。

-- 交叉连接（慎用，数据量会爆炸）
SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
-- 7 个员工 × 4 个部门 = 28 行

自连接

表与自身连接，常用于层级关系查询。

-- 查询员工及其直属经理
SELECT 
    e.name AS employee_name,
    m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

多表连接

-- 查询员工、部门和订单信息
SELECT 
    e.name AS employee_name,
    d.name AS department_name,
    o.amount,
    o.order_date
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN orders o ON e.id = o.employee_id
ORDER BY e.name, o.order_date;

JOIN 类型图示

    A 表        B 表
   ┌───┐      ┌───┐
   │   │      │   │
   │ ┌─┼──────┼─┐ │
   │ │ │ 交集 │ │ │
   │ └─┼──────┼─┘ │
   │   │      │   │
   └───┘      └───┘

INNER JOIN: 只取交集部分
LEFT JOIN:  取 A 表全部 + 交集
RIGHT JOIN: 取 B 表全部 + 交集
FULL JOIN:  取 A + B 全部

子查询

子查询是嵌套在其他查询中的 SELECT 语句。

WHERE 中的子查询

-- 查询薪资高于平均薪资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
 
-- 查询技术部的所有员工
SELECT name, salary
FROM employees
WHERE department_id = (
    SELECT id FROM departments WHERE name = '技术部'
);
 
-- 使用 IN 的子查询
SELECT name, salary
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE name IN ('技术部', '市场部')
);

EXISTS 子查询

检查子查询是否返回数据。

-- 查询有订单的员工
SELECT e.name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.employee_id = e.id
);
 
-- 查询没有订单的员工
SELECT e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.employee_id = e.id
);

FROM 中的子查询（派生表）

-- 查询各部门薪资最高的员工
SELECT dept_max.department_id, e.name, dept_max.max_salary
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) dept_max ON e.department_id = dept_max.department_id 
          AND e.salary = dept_max.max_salary;

SELECT 中的子查询（标量子查询）

-- 查询员工及其部门平均薪资
SELECT 
    name,
    salary,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department_id = e1.department_id) AS dept_avg
FROM employees e1;

相关子查询 vs 非相关子查询

-- 非相关子查询：子查询独立执行一次
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
 
-- 相关子查询：子查询依赖外层查询，每行执行一次
SELECT * FROM employees e1
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id
);

集合操作

UNION 并集

合并两个查询结果，去除重复行。

-- 查询技术部或薪资大于 15000 的员工
SELECT name, salary FROM employees WHERE department_id = 1
UNION
SELECT name, salary FROM employees WHERE salary > 15000;
 
-- UNION ALL 保留重复行
SELECT name FROM employees WHERE department_id = 1
UNION ALL
SELECT name FROM employees WHERE salary > 15000;

INTERSECT 交集（MySQL 8.0.31+）

返回两个查询的公共部分。

-- 查询既在技术部又薪资大于 15000 的员工
SELECT name FROM employees WHERE department_id = 1
INTERSECT
SELECT name FROM employees WHERE salary > 15000;
 
-- MySQL 旧版本替代方案
SELECT DISTINCT e1.name
FROM employees e1
JOIN employees e2 ON e1.id = e2.id
WHERE e1.department_id = 1 AND e2.salary > 15000;

EXCEPT 差集（MySQL 8.0.31+）

返回第一个查询中有而第二个查询中没有的结果。

-- 查询在技术部但薪资不超过 15000 的员工
SELECT name FROM employees WHERE department_id = 1
EXCEPT
SELECT name FROM employees WHERE salary > 15000;
 
-- MySQL 旧版本替代方案
SELECT name FROM employees 
WHERE department_id = 1 
  AND name NOT IN (
    SELECT name FROM employees WHERE salary > 15000
  );

综合示例

查询各部门最高薪员工

-- 方式1：使用子查询
SELECT e.name, e.salary, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE (e.department_id, e.salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);
 
-- 方式2：使用窗口函数（推荐）
SELECT name, salary, department
FROM (
    SELECT 
        e.name,
        e.salary,
        d.name AS department,
        RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rn
    FROM employees e
    JOIN departments d ON e.department_id = d.id
) ranked
WHERE rn = 1;

查询员工的订单汇总

SELECT 
    e.name,
    d.name AS department,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN orders o ON e.id = o.employee_id
GROUP BY e.id, e.name, d.name
ORDER BY total_amount DESC;

总结

类型用途返回结果
INNER JOIN匹配连接两表都有的行
LEFT JOIN左外连接左表全部 + 匹配
RIGHT JOIN右外连接右表全部 + 匹配
CROSS JOIN交叉连接笛卡尔积
子查询嵌套查询根据外层需求
UNION并集合并去重

性能建议

  1. 优先使用 JOIN 而非子查询（大多数情况下更高效）
  2. 为 JOIN 条件列创建索引
  3. 避免在 WHERE 中使用相关子查询
  4. 使用 EXISTS 替代 IN（当子查询数据量大时）

上一节：03-数据操作语言 下一节：05-聚合与分组 — GROUP BY 与聚合函数

sql join 子查询 高级查询

关系图谱

反向链接