| 用途 | 嵌套查询 |
|---|---|
| 类型 | 标量子查询 / IN / ANY / ALL / EXISTS |
| 改写 | JOIN / 派生表 |
标量与非相关子查询
标量与非相关子查询
SELECT id, amount, (SELECT AVG(amount) FROM orders) AS avg_all FROM orders;
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city='Hangzhou');相关子查询与 EXISTS
相关子查询与 EXISTS
SELECT u.id FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id=u.id AND o.amount>1000);MySQL 8 对 IN 子查询有半连接优化；但大结果集仍建议用 JOIN 或 EXISTS 做等价改写并对比执行计划。