SQL 的多表查询随着数据库技术的发展，结合如窗口函数、CTE、JSON 支持等，更高效且功能更强大。以下是基于主流 SQL 数据库（如 MySQL 8.0+、PostgreSQL、SQL Server 等）的多表查询的详细讲解。

---

1. 多表查询的基础概念

1.1 数据库结构

假设有以下两张表：

• users 表（用户信息）

  user_id | name      | email
  ----------------------------
  1       | Alice     | alice@example.com
  2       | Bob       | bob@example.com
  3       | Charlie   | charlie@example.com

• orders 表（订单信息）

  order_id | user_id | product_name   | price
  --------------------------------------------
  101      | 1       | Laptop         | 1000
  102      | 1       | Smartphone     | 500
  103      | 2       | Headphones     | 100

---

2. 常用的多表查询方法

2.1. INNER JOIN（内连接）

仅返回两张表中有匹配关系的记录。

SELECT 
    users.name,
    orders.product_name,
    orders.price
FROM 
    users
INNER JOIN 
    orders
ON 
    users.user_id = orders.user_id;

结果：

name      | product_name   | price
-----------------------------------
Alice     | Laptop         | 1000
Alice     | Smartphone     | 500
Bob       | Headphones     | 100

2.2. LEFT JOIN（左连接）

返回左表的所有记录，以及右表中匹配的记录。如果没有匹配，右表字段为 NULL。

SELECT 
    users.name,
    orders.product_name,
    orders.price
FROM 
    users
LEFT JOIN 
    orders
ON 
    users.user_id = orders.user_id;

结果：

name      | product_name   | price
-----------------------------------
Alice     | Laptop         | 1000
Alice     | Smartphone     | 500
Bob       | Headphones     | 100
Charlie   | NULL           | NULL

---

2.3. RIGHT JOIN（右连接）

返回右表的所有记录，以及左表中匹配的记录。如果没有匹配，左表字段为 NULL。

SELECT 
    users.name,
    orders.product_name,
    orders.price
FROM 
    users
RIGHT JOIN 
    orders
ON 
    users.user_id = orders.user_id;

结果：

name      | product_name   | price
-----------------------------------
Alice     | Laptop         | 1000
Alice     | Smartphone     | 500
Bob       | Headphones     | 100

---

2.4. FULL OUTER JOIN（全连接）

返回左右表的所有记录，无论是否匹配。如果某表无匹配项，对应字段为 NULL。
注意： MySQL 不直接支持 FULL JOIN，可通过 UNION 实现。

SELECT 
    users.name,
    orders.product_name,
    orders.price
FROM 
    users
LEFT JOIN 
    orders
ON 
    users.user_id = orders.user_id

UNION

SELECT 
    users.name,
    orders.product_name,
    orders.price
FROM 
    users
RIGHT JOIN 
    orders
ON 
    users.user_id = orders.user_id;

---

2.5. 使用 CTE 优化查询

CTE（Common Table Expression）让复杂查询更具可读性。

示例：查询每位用户的总订单金额

WITH UserOrders AS (
    SELECT 
        users.user_id,
        users.name,
        SUM(orders.price) AS total_spent
    FROM 
        users
    LEFT JOIN 
        orders
    ON 
        users.user_id = orders.user_id
    GROUP BY 
        users.user_id, users.name
)
SELECT 
    name, 
    total_spent
FROM 
    UserOrders
ORDER BY 
    total_spent DESC;

结果：

name      | total_spent
------------------------
Alice     | 1500
Bob       | 100
Charlie   | NULL

---

2.6. 使用窗口函数处理复杂统计

窗口函数让我们能在多表查询中动态计算排名、总计等信息。

示例：查询用户的订单总金额及排名

SELECT 
    users.name,
    SUM(orders.price) AS total_spent,
    RANK() OVER (ORDER BY SUM(orders.price) DESC) AS rank
FROM 
    users
LEFT JOIN 
    orders
ON 
    users.user_id = orders.user_id
GROUP BY 
    users.user_id, users.name;

结果：

name      | total_spent | rank
---------------------------------
Alice     | 1500        | 1
Bob       | 100         | 2
Charlie   | NULL        | 3

---

2.7. 多表嵌套查询（子查询）

示例：查询总订单金额超过 $500 的用户

SELECT 
    name
FROM 
    users
WHERE 
    user_id IN (
        SELECT 
            user_id
        FROM 
            orders
        GROUP BY 
            user_id
        HAVING 
            SUM(price) > 500
    );

结果：

name
-----
Alice

---

2.8. JSON 支持的多表查询

MySQL 8.0+ 和 PostgreSQL 支持将 JSON 数据类型与多表查询结合。

示例：以 JSON 格式返回用户及其订单

SELECT 
    users.name,
    JSON_ARRAYAGG(
        JSON_OBJECT('product_name', orders.product_name, 'price', orders.price)
    ) AS order_details
FROM 
    users
LEFT JOIN 
    orders
ON 
    users.user_id = orders.user_id
GROUP BY 
    users.name;

结果：

name      | order_details
--------------------------------------------
Alice     | [{"product_name": "Laptop", "price": 1000}, 
             {"product_name": "Smartphone", "price": 500}]
Bob       | [{"product_name": "Headphones", "price": 100}]
Charlie   | NULL

---

3. 最佳实践与优化

1. 索引优化：

• 为常用的 JOIN 字段（如 user_id）创建索引，加快查询速度。

   CREATE INDEX idx_user_id ON orders(user_id);

2. 分步调试：

• 复杂多表查询时，逐步构建 SQL，确保每一步结果正确。

2. 注意 NULL 处理：

• 使用 COALESCE 替换 NULL：

   SELECT 
       COALESCE(total_spent, 0) AS total_spent
   FROM 
       UserOrders;

4. 选择适当的工具：

• 使用现代 SQL 客户端（如 DBeaver、DataGrip）调试和可视化多表查询。

更多详细内容请关注其他相关文章。