-- IN 子查询
SELECT * FROM user
WHERE id IN (SELECT user_id FROM order WHERE amount > 1000);

-- EXISTS 子查询
SELECT * FROM user u
WHERE EXISTS (SELECT 1 FROM order o 
              WHERE o.user_id = u.id AND o.amount > 1000);

-- MySQL 处理 IN 子查询的逻辑：
-- 1. 先执行子查询：SELECT user_id FROM order WHERE amount > 1000
--    结果集：[1, 3, 5, 7, 9]
-- 2. 将结果集作为 IN 列表：WHERE id IN (1, 3, 5, 7, 9)
-- 3. 对外部表执行等值匹配

-- 优化器将 IN 子查询等效转换为
SELECT u.* FROM user u
SEMI JOIN order o ON u.id = o.user_id
WHERE o.amount > 1000;

-- MySQL 处理 EXISTS 子查询的逻辑：
-- 1. 取外部表 user 的一行
-- 2. 对每一行执行子查询：EXISTS (SELECT 1 FROM order WHERE user_id = u.id AND amount > 1000)
-- 3. 如果子查询有结果，该行加入结果集
-- 4. 重复 1-3 直到外部表遍历完毕

SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount > 1000)

假设：
- user 表：100 万行（大表）
- order 表：10 万行满足 amount > 1000（小结果集）

IN 的流程：子查询执行 1 次 → 结果集 10 万 → 与 user 表匹配
          (优：子查询结果集小)

EXISTS 的流程：外部表 user 100 万行 → 执行 100 万次子查询
              (劣：子查询执行次数过多)

-- 大表驱动的 EXISTS 很慢
SELECT * FROM user u   -- user 100 万行
WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);
-- 子查询会执行 100 万次！

-- 有索引时 EXISTS 效率提升
SELECT * FROM user u
WHERE EXISTS (
    SELECT 1 FROM order o 
    WHERE o.user_id = u.id  -- 如果 order.user_id 有索引，每次索引查找很快
);
-- 索引情况下，100 万次索引查找 ≈ 100 万 × 3-4 次 IO

-- 无索引时 EXISTS 非常慢
-- 100 万次 × 全表扫描 100 万行 = 不可接受的代价

-- NOT IN：需要子查询结果集无 NULL
SELECT * FROM user WHERE id NOT IN (SELECT user_id FROM order);
-- 如果子查询结果集中有 NULL，NOT IN 会返回空结果
-- 因为：NULL 参与 NOT IN 比较时，结果总是 UNKNOWN

-- NOT EXISTS：没有此问题
SELECT * FROM user u
WHERE NOT EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);
-- 不需要处理 NULL 的特殊情况

-- 返回 10 个大额订单的用户
SELECT * FROM user
WHERE id IN (SELECT user_id FROM order WHERE amount > 1000)
LIMIT 10;

-- IN 子查询会先计算全部结果集（可能很大），再 LIMIT
-- 换成 EXISTS + LIMIT 可能更优（外部遍历找到 10 条就停）

-- IN 写法
SELECT * FROM user WHERE id IN (SELECT user_id FROM order);

-- JOIN 改写（需要去重）
SELECT DISTINCT u.* FROM user u JOIN order o ON u.id = o.user_id;

-- EXISTS 写法
SELECT * FROM user u
WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

-- 三者语义等价，但优化器处理不同
-- MySQL 5.6+ 对三者的 SQL 执行计划可能完全相同