SQL联表查询方法详解

好的，SQL 联表查询是数据库操作中最核心和常用的技能之一。它允许你根据表之间的相关列，从多个表中组合并检索数据。

以下是 SQL 联表查询的主要方法，从最基础的到更高级的，并附有详细的示例。

---

一、联接的类型 (基于 ANSI SQL 标准)

最常用的联接是通过 JOIN 关键字实现的，通常与 ON 子句搭配来指定联接条件。

1. INNER JOIN (内连接)

作用：返回两个表中连接条件匹配的所有行。如果某行在其中一个表中没有匹配项，则不会返回。
** Venn 图**：两个集合的交集。

SELECT 
    orders.order_id, 
    customers.customer_name,
    orders.order_date
FROM orders
INNER JOIN customers 
    ON orders.customer_id = customers.customer_id;

结果：只会有那些下了订单的客户，以及每个订单对应的客户信息。没有下过订单的客户，或者没有关联客户的订单（理论上不应存在）都不会出现。

2. LEFT (OUTER) JOIN (左外连接)

作用：返回左表 (FROM 子句中的表) 的所有行，即使右表中没有匹配的行。如果右表中没有匹配，则结果集中右表的列返回 NULL。
** Venn 图**：左表的全部 + 与右表的交集。

SELECT 
    customers.customer_name,
    orders.order_id
FROM customers
LEFT JOIN orders 
    ON customers.customer_id = orders.customer_id;

结果：列出所有客户，包括那些从未下过订单的客户。对于没有订单的客户，order_id 字段将是 NULL。

3. RIGHT (OUTER) JOIN (右外连接)

作用：与左连接相反。返回右表 (JOIN 子句中的表) 的所有行，即使左表中没有匹配的行。如果左表中没有匹配，则结果集中左表的列返回 NULL。
** Venn 图**：右表的全部 + 与左表的交集。

SELECT 
    orders.order_id,
    employees.last_name AS sales_rep
FROM orders
RIGHT JOIN employees 
    ON orders.employee_id = employees.employee_id;

结果：列出所有员工，包括那些没有处理过任何订单的员工。对于没有订单的员工，order_id 字段将是 NULL。
注意：RIGHT JOIN 不如 LEFT JOIN 常用，因为通过调换表的位置，完全可以用 LEFT JOIN 实现同样的效果，这样代码更易读。

4. FULL (OUTER) JOIN (全外连接)

作用：返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，另一个表的列将包含 NULL。如果表之间有匹配，则整个行包含两个表的数据。
** Venn 图**：两个表的并集。

SELECT 
    customers.customer_name,
    orders.order_id
FROM customers
FULL OUTER JOIN orders 
    ON customers.customer_id = orders.customer_id;

结果：结合了 LEFT 和 RIGHT JOIN 的结果。会返回所有客户和所有订单。不匹配的地方用 NULL 填充。
注意：MySQL 不直接支持 FULL OUTER JOIN，但可以通过 LEFT JOIN + UNION + RIGHT JOIN 来模拟。

5. CROSS JOIN (交叉连接)

作用：返回两个表的笛卡尔积。即左表中的每一行与右表中的所有行组合。如果左表有 M 行，右表有 N 行，结果将是 M x N 行。
不需要 ON 子句。

SELECT 
    colors.color_name,
    sizes.size_name
FROM colors
CROSS JOIN sizes;

结果：生成所有颜色和所有尺寸的可能组合。常用于生成测试数据或所有可能的组合清单。

---

二、古老的语法 (在 WHERE 子句中联接)

在 ANSI-92 标准引入 JOIN...ON 语法之前，所有联接都是在 WHERE 子句中完成的。现在不推荐使用，因为它容易出错且可读性差，但你需要能识别它。

• 内连接等效写法：

  -- 不推荐的老式写法
  SELECT 
      orders.order_id, 
      customers.customer_name
  FROM orders, customers
  WHERE orders.customer_id = customers.customer_id;
  

• 左连接等效写法（(+) 是 Oracle 特有的操作符，其他数据库不支持）：

  -- 仅在 Oracle 中有效，绝对不推荐
  SELECT 
      customers.customer_name,
      orders.order_id
  FROM customers, orders
  WHERE customers.customer_id = orders.customer_id(+);
  

为什么推荐 JOIN...ON 语法？

1. 清晰分离：将联接逻辑 (ON) 和数据过滤逻辑 (WHERE) 分开，代码更易读和维护。
2. 避免错误：老式语法如果忘记写 WHERE 条件，就会变成 CROSS JOIN，产生巨大的错误结果集，而 INNER JOIN 忘记 ON 子句会直接报错。

---

三、多表联接和别名

你可以连续联接多个表。使用表别名可以简化代码，避免列名歧义。

示例：查询订单ID、客户名称和负责的员工姓名。

SELECT 
    o.order_id,
    c.customer_name,
    e.first_name || ' ' || e.last_name AS employee_name -- || 是字符串连接，在MySQL中是CONCAT()
FROM orders o -- 给 orders 表起别名 o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN employees e ON o.employee_id = e.employee_id;

---

四、使用 USING 关键字

当联接两个表的列名完全相同时，可以使用 USING 子句来简化 ON。

-- 假设 orders 和 customers 表中联接的列都叫 ‘customer_id’
SELECT 
    order_id,
    customer_name
FROM orders
INNER JOIN customers USING (customer_id); -- 等价于 ON orders.customer_id = customers.customer_id

---

五、自连接 (Self Join)

一个表和自己进行联接，常用于处理层次结构或树状数据（例如员工-经理关系、分类目录）。

示例：查询每个员工及其经理的姓名。

SELECT 
    e1.employee_name AS employee,
    e2.employee_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
-- 使用 LEFT JOIN 是因为最高级别的经理的 manager_id 可能是 NULL

总结与选择指南

联接类型	关键字	描述	何时使用
内连接	INNER JOIN	只返回两个表都匹配的行	查找有明确关联关系的记录（最常用）
左外连接	LEFT JOIN	返回左表所有行+右表匹配的行	查找主表所有记录，并附带关联信息（如所有客户及其订单）
右外连接	RIGHT JOIN	返回右表所有行+左表匹配的行	较少用，通常用左连接替代
全外连接	FULL OUTER JOIN	返回两个表的所有行，不匹配处用NULL填充	需要查看两个表完全并集时
交叉连接	CROSS JOIN	返回两个表的笛卡尔积	生成所有可能组合时
在 WHERE 中联接	FROM table1, table2 WHERE...	老式语法，不推荐	仅用于维护老代码时

最佳实践：

1. 始终使用 JOIN...ON 语法。
2. 明确你需要哪种连接：大多数情况下是 INNER JOIN 或 LEFT JOIN。
3. 为多表查询使用别名，使查询更简洁。
4. 小心多对多关系，联接多个表可能导致数据重复或结果集膨胀，使用 DISTINCT 或聚合函数来去重。