联表与查询

基础 SELECT / INSERT 见基础命令。

JOIN 类型

类型	语义
`INNER JOIN`	只保留匹配行
`LEFT JOIN`	左表全保留
`RIGHT JOIN`	右表全保留
`CROSS JOIN`	笛卡尔积，慎用

SELECT o.id, o.amount, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.created_at >= '2024-01-01';

Nested Loop

被驱动表 JOIN 键必须有索引
驱动表结果集宜小；大表无过滤 JOIN 危险

EXPLAIN SELECT ... FROM orders o JOIN users u ON o.user_id = u.id;

过滤尽早放在 ON / WHERE；LEFT JOIN 后勿在 WHERE 过滤右表列（会变 INNER）。

多表超过 3~4 张：拆查询、冗余字段，或分析库 ClickHouse。

EXISTS / IN

SELECT u.* FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'paid'
);

8.0 对 IN/EXISTS 有 semijoin 优化，以 EXPLAIN 为准。

子查询与派生表

标量子查询行数大时改 JOIN 聚合：

SELECT o.*, COALESCE(t.cnt, 0)
FROM orders o
LEFT JOIN (
  SELECT order_id, COUNT(*) AS cnt FROM order_item GROUP BY order_id
) t ON t.order_id = o.id;

派生表可能 derived_merge 或物化 MATERIALIZED。

CTE（WITH，8.0+）

WITH paid AS (
  SELECT user_id, SUM(amount) AS total
  FROM orders WHERE status = 'paid' GROUP BY user_id
)
SELECT u.name, p.total FROM users u JOIN paid p ON p.user_id = u.id;

递归 CTE 查树形结构。

跨表报表实践

多维度聚合

SELECT DATE(o.created_at) AS day, u.vip_level,
       COUNT(*) AS order_cnt, SUM(o.amount) AS gmv
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY DATE(o.created_at), u.vip_level;

同比（示意）

WITH daily AS (
  SELECT DATE(created_at) AS d, SUM(amount) AS gmv
  FROM orders WHERE status = 'paid' GROUP BY DATE(created_at)
)
SELECT d, gmv, LAG(gmv, 365) OVER (ORDER BY d) AS gmv_last_year
FROM daily;

真实同比常用日历表 LEFT JOIN 去年同日。进阶见开窗函数。

UNION

SELECT 'order' AS typ, id, amount AS val FROM orders WHERE user_id = 1
UNION ALL
SELECT 'refund', id, refund_amount FROM refunds WHERE user_id = 1;

性能注意

类型、字符集一致
大表先过滤再 JOIN
避免 SELECT *
重查询走从库或 OLAP

知识仓库

探索

MySQL 联表与查询