什么是子查询？

子查询（Subquery）是嵌套在另一个 SQL 查询中的查询，常用于 WHERE、SELECT 或 FROM 子句中。它能帮助你更灵活地处理复杂的数据检索需求。🔎

常见使用场景

  • 过滤数据：通过子查询结果筛选主查询的条件 
    SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = '技术部');
  • 计算值：在子查询中执行聚合操作 
    SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.id) AS order_count FROM customers;
  • 多表关联：替代传统 JOIN 操作 
    SELECT * FROM products WHERE product_id IN (SELECT item_id FROM sales WHERE sale_date > '2023-01-01');

子查询语法结构

  1. 基本格式 
    SELECT column(s) FROM table 
WHERE condition (SELECT column(s) FROM table2 WHERE condition);
  2. 多层嵌套
    可通过 WHERE 或 HAVING 子句嵌套多个子查询，但需注意性能优化 ⚠️

示例演示

  • 单行单列子查询 
    SELECT * FROM users WHERE id = (SELECT max(id) FROM orders);
  • 多行多列子查询 
    SELECT * FROM orders WHERE (customer_id, order_date) IN (SELECT id, created_at FROM users WHERE status = '活跃');

扩展阅读

SQL_子查询
Subquery_示例