CoderPath

第三章练习答案 ​

本页包含第三章所有练习的答案和详细解释。

01-inner-join ​

练习 1：基本连接 ​

答案：

sql
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

输出：

text
  name   |  product   | amount
---------+------------+---------
 Alice   | Laptop     | 1200.00
 Bob     | Mouse      |   25.00
 Alice   | Keyboard   |   80.00
 Charlie | Monitor    |  300.00
 Bob     | Headphones |   50.00

练习 2：带条件 ​

答案：

sql
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 50;

输出：

text
  name   | product
---------+----------
 Alice   | Laptop
 Alice   | Keyboard
 Charlie | Monitor

练习 3：聚合连接 ​

答案：

sql
SELECT u.name, SUM(o.amount) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY total_spent DESC;

输出：

text
  name   | total_spent
---------+-------------
 Alice   |     1280.00
 Charlie |      300.00
 Bob     |       75.00

练习 4：三表连接 ​

答案：

sql
SELECT u.name, o.product, c.name AS category, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN categories c ON o.category_id = c.id;

练习 5：自连接 ​

答案（使用 LEFT JOIN，下一节内容）：

sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

输出：

text
 employee | manager
----------+---------
 Alice    |        
 Bob      | Alice
 Charlie  | Alice
 Diana    | Bob

练习 6：WHERE 过滤 ​

答案：

sql
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.order_date > '2026-01-17';

输出：

text
  name   |  product
---------+------------
 Charlie | Monitor
 Bob     | Headphones

练习 7：多条件 ​

答案：

sql
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN categories c ON o.category_id = c.id
WHERE c.name = 'Electronics' AND o.amount > 100;

练习 8：统计 ​

答案：

sql
SELECT c.name AS category, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM categories c
INNER JOIN orders o ON c.id = o.category_id
GROUP BY c.name;

02-outer-join ​

练习 1：基本 LEFT JOIN ​

答案：

sql
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

输出：

text
  name   | order_count
---------+-------------
 Alice   |           2
 Bob     |           2
 Charlie |           1
 Diana   |           0

练习 2：找出孤儿 ​

答案：

sql
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

输出：

text
  name
--------
 Diana

练习 3：聚合统计 ​

答案：

sql
SELECT u.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name
ORDER BY total_spent DESC;

输出：

text
  name   | total_spent
---------+-------------
 Alice   |     1280.00
 Charlie |      300.00
 Bob     |       75.00
 Diana   |        0.00

解释：使用 COALESCE 将 NULL 转换为 0。

练习 4：多表 LEFT JOIN ​

答案：

sql
SELECT u.name, o.product, c.name AS category, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN categories c ON o.category_id = c.id;

输出：

text
  name   |  product   |  category   | amount
---------+------------+-------------+---------
 Alice   | Laptop     | Electronics | 1200.00
 Alice   | Keyboard   | Electronics |   80.00
 Bob     | Mouse      | Electronics |   25.00
 Bob     | Headphones | Electronics |   50.00
 Charlie | Monitor    | Electronics |  300.00
 Diana   |            |             |

练习 5：WHERE vs ON ​

答案：

查询 1（WHERE）：

sql
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

结果：Diana 被过滤掉（WHERE 在 JOIN 之后执行）

查询 2（ON）：

sql
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;

结果：所有用户都出现，但只显示符合条件的订单

区别：

  • WHERE：先 JOIN 再过滤，会丢失不匹配的左表行
  • ON：在 JOIN 时应用条件，保留所有左表行

练习 6：可选经理 ​

答案：

sql
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

练习 7：数据完整性 ​

答案：

sql
SELECT o.id, o.product, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

练习 8：实际场景 ​

答案：

sql
SELECT u.name, MAX(o.order_date) AS last_order_date, 
       (SELECT product FROM orders WHERE user_id = u.id ORDER BY order_date DESC LIMIT 1) AS last_product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

或更简洁（使用窗口函数，第四章内容）。

03-subqueries ​

练习 1：标量子查询 ​

答案：

sql
SELECT product, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

输出：

text
 product | amount
---------+---------
 Laptop  | 1200.00
 Monitor |  300.00

练习 2：IN 子查询 ​

答案：

sql
SELECT DISTINCT u.name
FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE order_date > '2026-01-17');

输出：

text
  name
---------
 Bob
 Charlie

练习 3：派生表 ​

答案：

sql
SELECT customer, order_count
FROM (
    SELECT u.name AS customer, COUNT(o.id) AS order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
) AS user_orders
WHERE order_count >= 2;

输出：

text
 customer | order_count
----------+-------------
 Alice    |           2
 Bob      |           2

练习 4：SELECT 子查询 ​

答案：

sql
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

输出：

text
  name   | order_count
---------+-------------
 Alice   |           2
 Bob     |           2
 Charlie |           1
 Diana   |           0

练习 5：相关子查询 ​

答案：

sql
SELECT u.name, 
    (SELECT product FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 1) AS most_expensive
FROM users u;

输出：

text
  name   | most_expensive
---------+----------------
 Alice   | Laptop
 Bob     | Headphones
 Charlie | Monitor
 Diana   |

练习 6：NOT IN ​

答案：

sql
SELECT name
FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

输出：

text
  name
--------
 Diana

练习 7：ANY/ALL ​

答案：

sql
-- 假设已有 categories 表并关联
SELECT o.product, o.amount
FROM orders o
WHERE o.amount > ALL (
    SELECT o2.amount 
    FROM orders o2 
    INNER JOIN categories c ON o2.category_id = c.id 
    WHERE c.name = 'Electronics'
);

练习 8：嵌套子查询 ​

答案：

sql
SELECT u.name
FROM users u
WHERE u.id IN (
    SELECT user_id
    FROM orders
    WHERE amount = (SELECT MAX(amount) FROM orders)
);

输出：

text
  name
--------
 Alice

04-exists-in ​

练习 1：基本 EXISTS ​

答案：

sql
SELECT u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

输出：

text
  name
---------
 Alice
 Bob
 Charlie

练习 2：NOT EXISTS ​

答案：

sql
SELECT u.name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

输出：

text
  name
--------
 Diana

练习 3：复杂条件 ​

答案：

sql
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
    GROUP BY o.user_id
    HAVING COUNT(*) >= 2
);

输出：

text
  name
--------
 Alice
 Bob

练习 4：IN vs EXISTS ​

答案：

使用 IN：

sql
SELECT u.name
FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE product = 'Laptop');

使用 EXISTS：

sql
SELECT u.name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.product = 'Laptop');

两者结果相同，现代数据库优化器通常会选择相似的执行计划。

练习 5：NULL 陷阱 ​

答案：

sql
-- 插入 NULL
INSERT INTO orders (user_id, product, amount, order_date) VALUES
    (NULL, 'Gift', 100.00, '2026-01-20');

-- NOT IN（错误：返回空集）
SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders);

-- NOT EXISTS（正确：返回 Diana）
SELECT name FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

解释：NOT IN 遇到 NULL 会返回空集，NOT EXISTS 不受影响。

练习 6：双向存在性 ​

答案：

sql
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    INNER JOIN categories c ON o.category_id = c.id
    WHERE o.user_id = u.id AND c.name = 'Electronics'
)
AND EXISTS (
    SELECT 1 FROM orders o 
    INNER JOIN categories c ON o.category_id = c.id
    WHERE o.user_id = u.id AND c.name = 'Furniture'
);

练习 7：数据完整性 ​

答案：

sql
SELECT o.id, o.product, o.user_id
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

练习 8：高级应用 ​

答案：

sql
SELECT o.product
FROM orders o
INNER JOIN categories c ON o.category_id = c.id
WHERE EXISTS (
    SELECT 1
    FROM orders o2
    WHERE o2.product = o.product AND o2.user_id != o.user_id
)
GROUP BY o.product, c.name
HAVING COUNT(DISTINCT o.user_id) >= 2;

05-union ​

练习 1：基本 UNION ​

答案：

sql
SELECT email FROM users
UNION
SELECT email FROM suppliers;

输出（去重）：

text
         email
-----------------------
 alice@example.com
 alice@supplier.com
 bob@example.com
 charlie@example.com
 contact@techcorp.com
 diana@example.com
 sales@globalparts.com

练习 2：UNION ALL ​

答案：

sql
SELECT name FROM users
UNION ALL
SELECT name FROM suppliers;

输出（保留重复）：

text
     name
--------------
 Alice
 Bob
 Charlie
 Diana
 TechCorp
 Alice        
 Global Parts

练习 3：添加来源标识 ​

答案：

sql
SELECT name, 'User' AS source FROM users
UNION ALL
SELECT name, 'Supplier' AS source FROM suppliers;

输出：

text
     name     | source
--------------+----------
 Alice        | User
 Bob          | User
 Charlie      | User
 Diana        | User
 TechCorp     | Supplier
 Alice        | Supplier
 Global Parts | Supplier

练习 4：带排序 ​

答案：

sql
SELECT name FROM users
UNION
SELECT name FROM suppliers
ORDER BY name;

练习 5：INTERSECT ​

答案（PostgreSQL）：

sql
SELECT email FROM users
INTERSECT
SELECT email FROM suppliers;

练习 6：EXCEPT ​

答案（PostgreSQL）：

sql
SELECT name FROM users
EXCEPT
SELECT name FROM suppliers;

输出：

text
  name
---------
 Bob
 Charlie
 Diana

练习 7：MySQL 替代 ​

答案：

INTERSECT 替代（使用 INNER JOIN）：

sql
SELECT DISTINCT u.name
FROM users u
INNER JOIN suppliers s ON u.name = s.name;

EXCEPT 替代（使用 NOT IN）：

sql
SELECT name FROM users
WHERE name NOT IN (SELECT name FROM suppliers);

练习 8：实际应用 ​

答案：

sql
SELECT product, SUM(amount) AS total
FROM orders
GROUP BY product
UNION ALL
SELECT '总计', SUM(amount)
FROM orders
ORDER BY product;

输出：

text
  product   |  total
------------+---------
 Headphones |   50.00
 Keyboard   |   80.00
 Laptop     | 1200.00
 Monitor    |  300.00
 Mouse      |   25.00
 总计       | 1655.00

复习要点 ​

第三章关键概念 ​

  1. INNER JOIN：只返回匹配的行
  2. LEFT JOIN：返回左表所有行，右表不匹配时为 NULL
  3. 子查询：标量、行、表子查询，相关与非相关
  4. EXISTS：高效检查存在性，短路求值
  5. UNION：垂直合并结果集，UNION 去重，UNION ALL 保留重复

常见陷阱 ​

  • ❌ LEFT JOIN + WHERE 过滤掉 NULL → ✅ 把条件放在 ON 中
  • ❌ NOT IN 遇到 NULL 返回空集 → ✅ 用 NOT EXISTS
  • ❌ 混淆 UNION（垂直）和 JOIN（水平）
  • ❌ 相关子查询性能差 → ✅ 考虑用 JOIN

返回第三章概览 · 返回SQL 学习路线

用心记录每一步成长

Copyright © 2026-present Twiink