CoderPath

INNER JOIN：内连接与笛卡尔积 ​

为什么需要表连接 ​

关系数据库将数据分散存储在多个表中，避免冗余。通过外键建立表之间的关系，通过 JOIN 在查询时重新组合数据。

创建示例表：

sql
-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com'),
    ('Diana', 'diana@example.com');

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    amount DECIMAL(10, 2),
    order_date DATE
);

INSERT INTO orders (user_id, product, amount, order_date) VALUES
    (1, 'Laptop', 1200.00, '2026-01-15'),
    (2, 'Mouse', 25.00, '2026-01-16'),
    (1, 'Keyboard', 80.00, '2026-01-17'),
    (3, 'Monitor', 300.00, '2026-01-18'),
    (2, 'Headphones', 50.00, '2026-01-19');

现在想查询"每个订单的用户名和金额"，需要连接两个表。

INNER JOIN 基础语法 ​

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

示例：

sql
SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;

结果：

text
  name   |  product   | amount
---------+------------+---------
 Alice   | Laptop     | 1200.00
 Bob     | Mouse      |   25.00
 Alice   | Keyboard   |   80.00
 Charlie | Monitor    |  300.00
 Bob     | Headphones |   50.00

说明：

  • FROM users：左表
  • INNER JOIN orders：右表
  • ON users.id = orders.user_id：连接条件
  • 只返回两表都匹配的行（Diana 没有订单，所以不出现）

理解笛卡尔积 ​

没有 ON 条件的 JOIN 会产生笛卡尔积（所有可能的组合）：

sql
-- 错误：忘记 ON 条件
SELECT users.name, orders.product
FROM users
CROSS JOIN orders;

结果：4 个用户 × 5 个订单 = 20 行（每个用户与每个订单组合）

text
  name   |  product
---------+------------
 Alice   | Laptop
 Alice   | Mouse
 Alice   | Keyboard
 Alice   | Monitor
 Alice   | Headphones
 Bob     | Laptop
 Bob     | Mouse
 ...（共 20 行）

ON 条件限制了组合规则，只保留 users.id = orders.user_id 的行。

表别名 ​

使用别名简化查询：

sql
SELECT u.name, o.product, o.amount
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id;

AS 可以省略：

sql
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

何时必须用别名：

  • 自连接（同一表连接自己）
  • 列名消歧（两表有同名列）
  • 提高可读性

列名消歧 ​

两表有同名列时，必须用表名或别名限定：

sql
-- 错误：id 存在于两个表
SELECT id, name, product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

错误信息：

text
ERROR:  column reference "id" is ambiguous

正确写法：

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

或使用别名：

sql
SELECT u.id, u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

连接多个表 ​

可以连接多个表：

sql
-- 创建产品类别表
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO categories (id, name) VALUES
    (1, 'Electronics'),
    (2, 'Furniture');

-- 给订单表添加类别
ALTER TABLE orders ADD COLUMN category_id INT;

UPDATE orders SET category_id = 1 WHERE product IN ('Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Headphones');

-- 连接三个表
SELECT u.name AS customer, o.product, c.name AS category, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN categories c ON o.category_id = c.id;

结果：

text
 customer |  product   |  category   | amount
----------+------------+-------------+---------
 Alice    | Laptop     | Electronics | 1200.00
 Bob      | Mouse      | Electronics |   25.00
 Alice    | Keyboard   | Electronics |   80.00
 Charlie  | Monitor    | Electronics |  300.00
 Bob      | Headphones | Electronics |   50.00

执行顺序：先连接 users 和 orders，结果再连接 categories。

JOIN 与 WHERE 结合 ​

可以在 JOIN 后用 WHERE 过滤：

sql
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

结果：

text
  name   | product | amount
---------+---------+---------
 Alice   | Laptop  | 1200.00
 Charlie | Monitor |  300.00

执行顺序：FROM → JOIN → WHERE → SELECT

JOIN 与聚合 ​

连接后可以聚合：

sql
-- 每个用户的订单数和总金额
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

结果：

text
  name   | order_count |  total
---------+-------------+---------
 Alice   |           2 | 1280.00
 Bob     |           2 |   75.00
 Charlie |           1 |  300.00

注意：Diana 没有出现（INNER JOIN 不包括没有订单的用户）。

INNER JOIN 的简写 ​

INNER 关键字可以省略：

sql
-- 以下三种写法等价
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
SELECT * FROM users JOIN orders ON users.id = orders.user_id;
SELECT * FROM users, orders WHERE users.id = orders.user_id;  -- 旧语法

推荐使用 INNER JOIN ... ON（最清晰）。

自连接 ​

表可以连接自己，必须用不同别名：

sql
-- 创建员工表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees (name, manager_id) VALUES
    ('Alice', NULL),      -- CEO
    ('Bob', 1),           -- Bob 的经理是 Alice
    ('Charlie', 1),
    ('Diana', 2);         -- Diana 的经理是 Bob

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

结果：

text
 employee | manager
----------+---------
 Bob      | Alice
 Charlie  | Alice
 Diana    | Bob

Alice（CEO）没有出现，因为她没有经理（manager_id 是 NULL）。

常见错误 ​

错误 1：忘记 ON 条件 ​

sql
-- 错误：产生笛卡尔积
SELECT * FROM users JOIN orders;

错误 2：ON 条件错误 ​

sql
-- 错误：连接条件反了
SELECT * FROM users u JOIN orders o ON u.id = o.id;  -- 应该是 o.user_id

错误 3：列名歧义 ​

sql
-- 错误：id 在两个表中都存在
SELECT id FROM users JOIN orders ON users.id = orders.user_id;

练习 ​

  1. 基本连接：查询所有订单，显示用户名、产品和金额。

  2. 带条件：查询金额大于 50 的订单，显示用户名和产品。

  3. 聚合连接：计算每个用户的总消费金额，按金额降序排序。

  4. 三表连接：连接 users、orders、categories，显示用户名、产品、类别、金额。

  5. 自连接：使用 employees 表，查询所有员工及其经理，包括 CEO（提示：使用 LEFT JOIN，下一节内容）。

  6. WHERE 过滤：查询在 2026-01-17 之后下单的用户名和产品。

  7. 多条件：查询 Electronics 类别中金额大于 100 的订单，显示用户名和产品。

  8. 统计：查询每个类别的订单数量和总金额。

完成练习后，打开本章答案页核对。

下一步 ​

学会了 INNER JOIN。下一节学习 LEFT JOIN 和 RIGHT JOIN：如何处理可选关系，包括没有订单的用户。

回到第三章概览 · 返回SQL 学习路线

用心记录每一步成长

Copyright © 2026-present Twiink