SQL 多表：JOIN 与子查询 · Java 数据库实战

第 3 章 · JOIN、子查询与电商多表联查

本章目标：掌握 INNER JOIN、LEFT JOIN（及 RIGHT JOIN 概念）；多表关联书写与表别名；标量/行/表子查询；EXISTS / NOT EXISTS；完成 orders + order_lines + products + users 电商典型联查；理解 JOIN 与 WHERE 笛卡尔积陷阱；对照 spring-boot-web 关联查询与 python-database ch03 平行结构。

学时建议：4～5 小时（含 3 小时跟练）

前置：完成 java-database ch02；样本表 users/products/orders/order_lines 已存在。

3.1 场景说明：订单详情页需要四表数据

用户打开虚构订单详情 GET https://api.example.com/api/v1/orders/3，后台需一次查出：

信息	来源表
买家邮箱	`users.email`
订单状态、总额	`orders`
明细：商品 slug、标题、数量、单价	`order_lines` JOIN `products`

本章用纯 SQL 实现上述联查；JPA 写法见 spring-boot-web @ManyToOne / @OneToMany，java-database ch08+ 系统讲 JDBC/JPA 关联加载。

users ──1:N──► orders ──1:N──► order_lines ──N:1──► products
  id            user_id         order_id          product_id
                                 product_id ──────────► id

全站约定：products.slug UNIQUE、is_published；users.password_hash 不出现在列表 API；order_lines.unit_price 为下单快照价（分）。

3.2 笛卡尔积与 JOIN 动机

-- 危险：无关联条件，6 products × 3 orders = 18 行
SELECT p.slug, o.id AS order_id
FROM products p, orders o;

-- 正确：显式 INNER JOIN
SELECT p.slug, o.id AS order_id, ol.quantity
FROM orders o
INNER JOIN order_lines ol ON ol.order_id = o.id
INNER JOIN products p ON p.id = ol.product_id;

JOIN 类型	结果
INNER JOIN	仅两边都匹配的行
LEFT JOIN	左表全保留，右表无匹配则 NULL
RIGHT JOIN	右表全保留（可用 LEFT 改写）
CROSS JOIN	笛卡尔积，慎用

3.3 INNER JOIN：订单明细带商品名

SELECT
  o.id AS order_id,
  o.status,
  o.total_amount,
  ol.quantity,
  ol.unit_price,
  p.slug,
  p.title,
  p.is_published
FROM orders o
INNER JOIN order_lines ol ON ol.order_id = o.id
INNER JOIN products p ON p.id = ol.product_id
ORDER BY o.id, ol.id;

表别名：orders o 缩短列引用；多表同名列必须带前缀 o.id。

金额展示：

SELECT
  o.id,
  p.slug,
  ol.quantity,
  ol.unit_price AS unit_cents,
  (ol.quantity * ol.unit_price) AS line_total_cents,
  ROUND(ol.quantity * ol.unit_price / 100, 2) AS line_total_yuan
FROM orders o
INNER JOIN order_lines ol ON ol.order_id = o.id
INNER JOIN products p ON p.id = ol.product_id
WHERE o.id = 1;

3.4 LEFT JOIN：含无明细订单 / 无销量商品

-- 所有订单，即使没有 order_lines（本样本每单都有明细）
SELECT o.id, o.status, ol.id AS line_id, p.slug
FROM orders o
LEFT JOIN order_lines ol ON ol.order_id = o.id
LEFT JOIN products p ON p.id = ol.product_id
ORDER BY o.id;

-- 从未卖出一单的商品（左表 products，右表匹配 order_lines）
SELECT p.id, p.slug, p.title
FROM products p
LEFT JOIN order_lines ol ON ol.product_id = p.id
WHERE ol.id IS NULL;

-- 已上架但零销量
SELECT p.slug, p.title
FROM products p
LEFT JOIN order_lines ol ON ol.product_id = p.id
WHERE p.is_published = 1
GROUP BY p.id, p.slug, p.title
HAVING COUNT(ol.id) = 0;

LEFT JOIN 要点：过滤右表条件放 ON 还是 WHERE 语义不同：

-- 仅 JOIN 已上架商品，未上架行 ol 为 NULL 仍保留订单行
SELECT o.id, p.slug
FROM orders o
LEFT JOIN order_lines ol ON ol.order_id = o.id
LEFT JOIN products p ON p.id = ol.product_id AND p.is_published = 1;

3.5 四表联查：订单 + 用户 + 明细 + 商品

订单 #3 完整详情（对应 api.example.com 管理后台）：

SELECT
  o.id AS order_id,
  o.status,
  o.total_amount AS order_total_cents,
  o.created_at,
  u.email AS buyer_email,
  ol.id AS line_id,
  p.slug AS product_slug,
  p.title AS product_title,
  ol.quantity,
  ol.unit_price AS unit_cents,
  (ol.quantity * ol.unit_price) AS line_cents
FROM orders o
INNER JOIN users u ON u.id = o.user_id
INNER JOIN order_lines ol ON ol.order_id = o.id
INNER JOIN products p ON p.id = ol.product_id
WHERE o.id = 3
ORDER BY ol.id;

用户订单列表（含行数）：

SELECT
  u.email,
  o.id AS order_id,
  o.status,
  o.total_amount,
  COUNT(ol.id) AS line_count,
  SUM(ol.quantity * ol.unit_price) AS computed_total_cents
FROM users u
INNER JOIN orders o ON o.user_id = u.id
LEFT JOIN order_lines ol ON ol.order_id = o.id
GROUP BY u.id, u.email, o.id, o.status, o.total_amount
ORDER BY o.created_at DESC;

验证：computed_total_cents 应与 orders.total_amount 一致（ch04 事务写入时保证）。

3.6 子查询：标量、行、表

标量子查询（返回单值，用于 SELECT 或 WHERE）：

-- 高于均价的商品
SELECT slug, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 单笔最大订单金额
SELECT MAX(total_amount) FROM orders;

SELECT o.id, o.total_amount
FROM orders o
WHERE o.total_amount = (SELECT MAX(total_amount) FROM orders);

行子查询（返回一行多列，少用）：

SELECT slug, title FROM products
WHERE (category, is_published) = (
  SELECT category, is_published FROM products WHERE slug = 'java-concurrency'
);

表子查询（FROM 子句内，派生表）：

SELECT t.user_id, t.order_cnt
FROM (
  SELECT user_id, COUNT(*) AS order_cnt
  FROM orders
  GROUP BY user_id
) AS t
WHERE t.order_cnt >= 2;

MySQL 要求派生表必须有别名 AS t。

3.7 IN / NOT IN 与子查询

-- 买过「books」类商品的用户 email
SELECT DISTINCT u.email
FROM users u
WHERE u.id IN (
  SELECT o.user_id
  FROM orders o
  INNER JOIN order_lines ol ON ol.order_id = o.id
  INNER JOIN products p ON p.id = ol.product_id
  WHERE p.category = 'books'
);

-- 从未下单的用户
SELECT email FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

-- 注意：NOT IN 若子查询含 NULL，结果可能为空集
SELECT email FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);