接下来是高级SQL查询技巧：JOIN、子查询与联合查询详解部分的详细教程。我们将详细探讨连接查询、子查询、联合查询以及窗口函数等高级SQL技巧。

---

1. 连接查询（JOIN）

连接查询（JOIN）是SQL中最常见的查询类型，它用于根据表之间的关系从多个表中获取数据。SQL提供了不同类型的连接查询，常见的包括 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN。

1.1 INNER JOIN

INNER JOIN 返回两个表中满足连接条件的交集部分，只有在两个表中都有匹配的记录时才会显示。

• 示例：查询所有用户及其对应的订单信息：

SELECT users.id, users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

该查询返回所有用户及其订单信息，只有那些有订单的用户才会被返回。

1.2 LEFT JOIN（或 LEFT OUTER JOIN）

LEFT JOIN 返回左表的所有记录，以及右表中与左表记录匹配的记录。如果右表中没有匹配的记录，结果中将包含左表的记录，并将右表的列返回 NULL。

• 示例：查询所有用户及其订单信息（包括没有订单的用户）：

SELECT users.id, users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

如果用户没有订单，order_id 将显示为 NULL。

1.3 RIGHT JOIN（或 RIGHT OUTER JOIN）

RIGHT JOIN 返回右表的所有记录，以及左表中与右表记录匹配的记录。如果左表中没有匹配的记录，结果中将包含右表的记录，并将左表的列返回 NULL。

• 示例：查询所有订单及其对应的用户信息（包括没有用户的订单）：

SELECT users.id, users.name, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

如果订单没有对应的用户，users.id 和 users.name 将显示为 NULL。

1.4 FULL OUTER JOIN

FULL OUTER JOIN 返回左表和右表中的所有记录，当其中一个表没有匹配时，返回 NULL。

• 示例：查询所有用户和所有订单信息（包括没有订单的用户和没有用户的订单）：

SELECT users.id, users.name, orders.order_id
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

无论是没有订单的用户，还是没有用户的订单，都会被返回，并且没有匹配的列会显示为 NULL。

1.5 自连接（Self Join）

自连接是指一个表与它自身连接。通常在需要比较表中的不同记录时使用。

• 示例：查询员工与其经理的信息（假设 employees 表包含员工及其经理的ID）：

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

这里，employees 表与自己连接，e 表示员工，m 表示经理。

2. 子查询

子查询（Subquery）是嵌套在其他查询中的查询，它允许你在一个查询中使用另一个查询的结果。子查询可以放在 SELECT、WHERE 和 FROM 子句中。

2.1 在 SELECT 子句中使用子查询

在 SELECT 子句中，子查询返回一个单一值，并用于计算其他列的值。

• 示例：查询每个用户的订单总数：

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

这个查询对于每个用户，使用子查询计算该用户的订单数量。

2.2 在 WHERE 子句中使用子查询

子查询可以作为 WHERE 子句的条件，通常用于过滤数据。

• 示例：查询购买过超过 5 个订单的用户：

SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 5);

这个查询通过子查询获取购买超过 5 个订单的用户ID，并返回符合条件的用户。

2.3 在 FROM 子句中使用子查询

子查询也可以放在 FROM 子句中，作为虚拟表使用。

• 示例：查询每个用户的平均订单金额：

SELECT user_id, AVG(order_amount) AS avg_order_amount
FROM (SELECT user_id, order_amount FROM orders) AS order_data
GROUP BY user_id;

这里，子查询将订单数据作为虚拟表 order_data 使用。

2.4 相关子查询与非相关子查询

• 相关子查询：子查询中的值依赖于外部查询的列。
• 非相关子查询：子查询的结果独立于外部查询。

例如，相关子查询示例：

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

该查询检查每个用户是否至少有一个订单。

3. 联合查询（UNION）

联合查询用于将两个或多个查询的结果合并为一个结果集，要求所有查询的列数和列类型一致。

3.1 使用 UNION

UNION 用于合并两个查询的结果集，默认去除重复的记录。

• 示例：查询所有来自美国或英国的用户：

SELECT name FROM users WHERE country = 'USA'
UNION
SELECT name FROM users WHERE country = 'UK';

UNION 会去掉重复的用户，返回唯一的用户名称。

3.2 使用 UNION ALL

UNION ALL 用于合并两个查询的结果集，但不去重，返回所有记录。

• 示例：查询所有来自美国或英国的用户（包括重复记录）：

SELECT name FROM users WHERE country = 'USA'
UNION ALL
SELECT name FROM users WHERE country = 'UK';

UNION ALL 会包括所有记录，不去掉重复项。

4. 集合操作

集合操作用于在 SQL 中执行集合之间的运算，类似于数学中的集合操作，包括交集（INTERSECT）和差集（EXCEPT）。

4.1 INTERSECT

INTERSECT 返回两个查询的交集，即两个查询都包含的记录。

• 示例：查询购买过“商品A”且购买过“商品B”的用户：

SELECT user_id FROM orders WHERE product_id = 'A'
INTERSECT
SELECT user_id FROM orders WHERE product_id = 'B';

该查询返回同时购买过商品A和商品B的用户。

4.2 EXCEPT

EXCEPT 返回第一个查询结果中存在但第二个查询结果中不存在的记录。

• 示例：查询购买过商品A但没有购买过商品B的用户：

SELECT user_id FROM orders WHERE product_id = 'A'
EXCEPT
SELECT user_id FROM orders WHERE product_id = 'B';

该查询返回那些购买过商品A但没有购买过商品B的用户。

5. 窗口函数

窗口函数（Window Function）允许你在查询中对数据进行排序、分组或聚合，而不会丢失原始的行信息。常见的窗口函数包括 ROW_NUMBER()、RANK() 和 DENSE_RANK()。

5.1 ROW_NUMBER()

ROW_NUMBER() 为每一行分配一个唯一的数字，通常按特定的顺序排列。

• 示例：为每个用户的订单分配一个行号：

SELECT user_id, order_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS row_num
FROM orders;

此查询根据 order_date 对每个用户的订单进行排序，并为每个订单分配一个唯一的行号。

5.2 RANK() 和 DENSE_RANK()

• RANK() 给相同值的行分配相同的排名，并在排名中留下空缺。
• DENSE_RANK() 给相同值的行分配相同的排名，但不会留下空缺。
• 示例：按订单金额为用户排名：

SELECT user_id, order_amount, RANK() OVER (ORDER BY order_amount DESC) AS rank
FROM orders;

RANK() 会对订单金额进行排名，且相同金额的订单会有相同的排名，但排名之间可能有空缺。

5.3 使用窗口函数进行排序、分区和聚合

窗口函数非常适合在不丢失原始行的情况下进行排序、分区和聚合。

• 示例：计算每个用户的订单金额

排名，并在每个用户内按订单金额排序：

SELECT user_id, order_id, order_amount,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_amount DESC) AS rank
FROM orders;

此查询会根据每个用户的订单金额对订单进行排名。

---

总结

在本教程中，你学会了 SQL 中一些重要的高级查询技巧，包括如何使用不同类型的连接查询、如何编写子查询、如何使用 UNION 合并查询结果、如何执行集合操作（如 INTERSECT 和 EXCEPT），以及如何利用窗口函数进行高级排序和聚合操作。掌握这些高级 SQL 技巧，将大大提升你在复杂查询和数据分析中的能力。