跳到主要内容

JOIN 连接

JOIN 用于根据两个或多个表之间的关系，从这些表中查询数据。本章将详细介绍各种连接类型。

为什么需要 JOIN？​

关系型数据库将数据分散在多个表中，通过外键关联。JOIN 让我们能够将这些分散的数据组合起来。

-- 用户表
-- | id | name  |
-- |----|-------|
-- | 1  | 张三  |
-- | 2  | 李四  |

-- 订单表
-- | id | user_id | product |
-- |----|---------|---------|
-- | 1  | 1       | 手机    |
-- | 2  | 1       | 电脑    |
-- | 3  | 2       | 平板    |

-- 使用 JOIN 查询用户及其订单
SELECT users.name, orders.product
FROM users
JOIN orders ON users.id = orders.user_id;
-- | name | product |
-- |------|---------|
-- | 张三 | 手机    |
-- | 张三 | 电脑    |
-- | 李四 | 平板    |

INNER JOIN（内连接）​

内连接只返回两个表中匹配的行：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- 示例
SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

特点：

  • 只返回匹配的行
  • 不匹配的行被排除
  • 最常用的连接类型

LEFT JOIN（左连接）​

左连接返回左表的所有行，右表没有匹配则返回 NULL：

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- 示例：查询所有用户及其订单（包括没有订单的用户）
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- 结果
-- | name | product |
-- |------|---------|
-- | 张三 | 手机    |
-- | 张三 | 电脑    |
-- | 李四 | 平板    |
-- | 王五 | NULL    |  <- 没有订单的用户

查找不匹配的行​

-- 查找没有订单的用户
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

RIGHT JOIN（右连接）​

右连接返回右表的所有行，左表没有匹配则返回 NULL：

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- 示例
SELECT users.name, orders.product
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
提示

右连接可以用左连接替代，只需交换表的位置。大多数开发者更习惯使用左连接。

FULL OUTER JOIN（全外连接）​

全外连接返回两个表的所有行，没有匹配则返回 NULL：

-- PostgreSQL / Oracle / SQL Server
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

-- MySQL 不支持 FULL OUTER JOIN，使用 UNION 模拟
SELECT columns FROM table1 LEFT JOIN table2 ON ...
UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON ...;

CROSS JOIN（交叉连接）​

交叉连接返回两个表的笛卡尔积（所有可能的组合）：

SELECT columns
FROM table1
CROSS JOIN table2;

-- 示例：生成所有可能的组合
SELECT sizes.size, colors.color
FROM sizes
CROSS JOIN colors;

-- 如果 sizes 有 3 行，colors 有 4 行
-- 结果有 3 × 4 = 12 行

自连接​

表与自身连接，用于处理层级数据：

-- 员工表包含上级 ID
-- | id | name  | manager_id |
-- |----|-------|------------|
-- | 1  | 张三  | NULL       |
-- | 2  | 李四  | 1          |
-- | 3  | 王五  | 1          |

-- 查询员工及其上级
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 结果
-- | employee | manager |
-- |----------|---------|
-- | 张三     | NULL    |
-- | 李四     | 张三    |
-- | 王五     | 张三    |

多表连接​

可以连接多个表：

SELECT 
    users.name,
    orders.order_date,
    products.product_name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

连接顺序​

-- 从左到右依次连接
FROM table1
JOIN table2 ON ...
JOIN table3 ON ...
JOIN table4 ON ...

-- 使用括号控制顺序
FROM table1
JOIN (table2 JOIN table3 ON table2.id = table3.t2_id)
ON table1.id = table2.t1_id

连接条件​

使用 ON 子句​

-- 单条件
JOIN table2 ON table1.id = table2.id

-- 多条件
JOIN table2 ON table1.id = table2.id AND table2.status = 'active'

使用 USING 子句​

当连接列名相同时：

-- 使用 ON
JOIN orders ON users.id = orders.user_id

-- 使用 USING（列名相同）
JOIN orders USING (user_id)

-- 多列
JOIN order_items USING (order_id, product_id)

自然连接​

自动匹配同名列：

-- 自动连接所有同名列
SELECT * FROM users NATURAL JOIN orders;
注意

自然连接不推荐使用，因为：

  • 隐式行为难以理解
  • 表结构变化可能导致意外结果
  • 建议显式使用 ON 或 USING

连接与过滤​

ON vs WHERE​

-- ON：连接条件，影响连接结果
-- WHERE：过滤条件，在连接后过滤

-- 左连接 + ON 条件
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id AND orders.status = 'completed';
-- 返回所有用户，没有完成订单的用户订单列为 NULL

-- 左连接 + WHERE 条件
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.status = 'completed';
-- 只返回有完成订单的用户（变成了内连接效果）

正确的过滤方式​

-- 查询所有用户及其完成订单（包括没有完成订单的用户）
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed';

-- 查询只有完成订单的用户
SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

复杂连接示例​

统计每个用户的订单数​

SELECT 
    users.name,
    COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

查找没有关联记录的行​

-- 没有订单的用户
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

-- 没有购买过某产品的用户
SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN order_items ON orders.id = order_items.order_id 
    AND order_items.product_id = 123
WHERE order_items.id IS NULL;

性能优化​

使用索引​

-- 确保连接列有索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_order_id ON order_items(order_id);

选择合适的连接类型​

-- 如果只需要匹配的行，使用 INNER JOIN
SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 如果需要保留所有左表行，使用 LEFT JOIN
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id;

减少连接的表数量​

-- 避免：连接太多表
SELECT * FROM a
JOIN b ON ...
JOIN c ON ...
JOIN d ON ...
JOIN e ON ...
JOIN f ON ...;

-- 考虑：拆分查询或使用子查询

小结​

本章我们学习了：

  1. INNER JOIN（内连接）
  2. LEFT JOIN（左连接）
  3. RIGHT JOIN（右连接）
  4. FULL OUTER JOIN（全外连接）
  5. CROSS JOIN（交叉连接）
  6. 自连接
  7. 多表连接
  8. 连接条件（ON、USING）
  9. 连接与过滤的区别
  10. 性能优化

练习​

  1. 查询所有用户及其订单信息
  2. 查询没有订单的用户
  3. 查询每个用户的订单总数
  4. 使用自连接查询员工及其上级
  5. 查询购买了特定产品的用户