SQL自学：什么是子查询，如何使用它们

在 SQL（Structured Query Language，结构化查询语言）的世界里，子查询是一种强大的工具，它允许我们在一个 SQL 查询内部嵌套另一个查询。子查询也被称为内部查询或嵌套查询，为我们提供了一种灵活且强大的方式来处理复杂的数据检索和操作任务。

一、什么是子查询

子查询是一个嵌套在另一个 SQL 查询（通常是 SELECT、INSERT、UPDATE 或 DELETE 语句）中的查询。它的结果被用作外部查询的一部分，以进一步筛选、聚合或连接数据。子查询可以返回单个值、一行或一列数据，甚至是一个完整的结果集，具体取决于它在外部查询中的使用方式。

例如，以下是一个简单的子查询示例：

SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM orders WHERE quantity > 10);

在这个例子中，内部查询 SELECT product_id FROM orders WHERE quantity > 10 返回了订单数量大于 10 的产品 ID，外部查询则使用这些产品 ID 从 products 表中检索出对应的产品名称。

二、子查询的类型 

1、标量子查询（Scalar Subquery）

• 标量子查询返回单个值，通常用于与比较运算符一起使用，例如 =、<、 > 等。
• 例如：

  SELECT product_name
  FROM products
  WHERE price > (SELECT AVG(price) FROM products);

  首先，它会在子查询中计算出 “products” 表中所有产品价格的平均值。然后，在主查询中，从 “products” 表里面逐个检查每一个产品，如果这个产品的价格比刚才计算出来的平均价格要高，那么就把这个产品的名称选出来。这个查询找出价格高于平均价格的产品名称。

2、列子查询（Column Subquery）

• 列子查询返回一列数据，可以在外部查询中与 IN, NOT IN, ANY, ALL 等运算符一起使用。
• 例如：

  SELECT product_name
  FROM products
  WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

  首先看子查询部分，SELECT category_id FROM categories WHERE category_name = 'Electronics'，它从 “categories” 表中找出类别名称为 “Electronics” 的那些行，并提取出对应的类别编号。然后在主查询中，SELECT product_name FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics')，从 “products” 表中选择产品名称，但只有当产品的类别编号在子查询得到的那些类别编号之中时，才会被选中返回。这个查询找出属于 “电子” 类别的产品名称。

3、行子查询（Row Subquery）

• 行子查询返回一行数据，可以在外部查询中与比较运算符一起使用，例如 =, <, > 等。
• 例如：

  SELECT product_name
  FROM products
  WHERE (product_id, price) = (SELECT product_id, MAX(price) FROM products);

  首先看子查询部分，SELECT product_id, MAX(price) FROM products，它从 “products” 表中找出价格的最大值，同时也返回具有这个最大价格的产品的编号。然后在主查询中，SELECT product_name FROM products WHERE (product_id, price) = (SELECT product_id, MAX(price) FROM products)，从 “products” 表中选择产品名称，但只有当产品的编号和价格与子查询返回的产品编号和最大价格完全一致时，这个产品的名称才会被选中返回。这个查询找出价格最高的产品名称。

4、表子查询（Table Subquery）

• 表子查询返回一个结果集，可以在外部查询中作为一个临时表使用。
• 例如：

  SELECT *
  FROM (SELECT product_id, product_name, price FROM products WHERE price > 100) AS temp_table;

  具体来说，首先有一个子查询，SELECT product_id, product_name, price FROM products WHERE price > 100，这个子查询从 “products” 表中筛选出价格大于 100 的产品，并提取出这些产品的编号、名称和价格。然后，使用AS temp_table给这个子查询的结果取一个别名叫做 “temp_table”，就好像给这个临时的结果集起了一个名字。最后，主查询SELECT * FROM temp_table从这个临时结果集 “temp_table” 中选择所有的列，也就是把价格大于 100 的那些产品的编号、名称和价格全部展示出来。这个查询创建一个临时表，包含价格大于 100 的产品信息，并从临时表中选择所有列。

三、如何使用子查询

1、用于筛选数据

• 子查询可以作为 WHERE 子句的一部分，用于筛选满足特定条件的数据。
• 例如：

  SELECT employee_name
  FROM employees
  WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

  首先，通过子查询从 “departments” 表中找出部门名称是 “Sales” 的部门的编号。然后，在主查询中，从 “employees” 表中选择员工名字，但只选择那些员工所在部门的编号在子查询结果中的员工。 

       这个查询找出属于 “销售” 部门的员工姓名。

2、用于聚合数据

• 子查询可以用于计算聚合值，并在外部查询中使用这些值进行进一步的计算或筛选。
• 例如：

  SELECT product_name
  FROM products WHERE price > (SELECT AVG(price) FROM products);

  首先，看子查询部分SELECT AVG(price) FROM products，它从 “products” 表中计算出所有产品价格的平均值。接着，在主查询中，SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products)，从 “products” 表中选择 “product_name”（产品名称）列的数据，但有一个条件限制，即只有当该产品的价格大于子查询所计算出的平均价格时，才会被选中返回。

  这个查询找出价格高于平均价格的产品名称。

 3、用于连接数据

• 子查询可以在 JOIN 子句中使用，以连接两个表并筛选满足特定条件的数据。
• 例如：

  SELECT e.employee_name, d.department_name
  FROM employees e JOIN departments d ON e.department_id = d.department_id
  WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

  首先，通过JOIN关键字将 “employees” 表和 “departments” 表连接起来，连接条件是员工表中的 “department_id”（员工所在部门编号）等于部门表中的 “department_id”。然后，设置了一个筛选条件，即员工的工资 “e.salary” 要大于子查询的结果。子查询SELECT AVG(salary) FROM employees WHERE department_id = e.department_id是计算出与当前员工所在部门相同的那些员工的平均工资。

       这个查询找出每个部门中工资高于该部门平均工资的员工姓名和部门名称。

练习题：

1．使用子查询，返回购买价格为10美元或以上产品的顾客列表。你需要使用OrderItems表查找匹配的订单号（order_num）​，然后使用Order表检索这些匹配订单的顾客ID（cust_id）​。

2．你想知道订购BR01产品的日期。编写SQL语句，使用子查询来确定哪些订单（在OrderItems中）购买了prod_id为BR01的产品，然后从Orders表中返回每个产品对应的顾客ID（cust_id）和订单日期（order_date）​。按订购日期对结果进行排序。

3．现在我们让它更具挑战性。在上一个挑战题，返回购买prod_id为BR01的产品的所有顾客的电子邮件（Customers表中的cust_email）​。提示：这涉及SELECT语句，最内层的从OrderItems表返回order_num，中间的从Customers表返回cust_id。

4．我们需要一个顾客ID列表，其中包含他们已订购的总金额。编写SQL语句，返回顾客ID（Orders表中的cust_id）​，并使用子查询返回total_ordered以便返回每个顾客的订单总数。将结果按金额从大到小排序。提示：之前已经使用SUM()计算订单总数。

5．编写SQL语句，从Products表中检索所有的产品名称（prod_name）​，以及名为quant_sold的计算列，其中包含所售产品的总数（在OrderItems表上使用子查询和SUM(quantity)检索）​。