多表联查JOIN

嗨！长安来了！今天我们学习数据库中最重要的技能之一：JOIN（联表查询）！

真实项目中，数据往往分散在多个表里，JOIN能把它们关联起来！🔗

🎯 为什么需要JOIN？

想象你在管理一个图书馆系统：

用户表（users）          借阅记录表（borrows）
┌────┬────────┐          ┌────┬─────────┬─────────┐
│ id │ name   │          │ id │ user_id │ book_id │
├────┼────────┤          ├────┼─────────┼─────────┤
│ 1  │ 长安   │          │ 1  │ 1       │ 101     │
│ 2  │ 小明   │          │ 2  │ 2       │ 102     │
│ 3  │ 小红   │          │ 3  │ 1       │ 103     │
└────┴────────┘          └────┴─────────┴─────────┘

问题：如何查询"谁借了哪本书"？

这就需要JOIN把两个表关联起来！

📝 准备测试数据

-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

-- 文章表
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    likes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 评论表
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id),
    user_id INT REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据
INSERT INTO users (username, email) VALUES 
    ('长安', 'changan@email.com'),
    ('小明', 'xiaoming@email.com'),
    ('小红', 'xiaohong@email.com'),
    ('小李', 'xiaoli@email.com');

INSERT INTO posts (user_id, title, content, likes) VALUES 
    (1, 'PostgreSQL入门', '这是内容...', 100),
    (1, 'SQL高级技巧', '这是内容...', 50),
    (2, '我的学习笔记', '这是内容...', 30),
    (3, '编程心得', '这是内容...', 20);

INSERT INTO comments (post_id, user_id, content) VALUES 
    (1, 2, '写得很好！'),
    (1, 3, '学到了！'),
    (2, 2, '赞！'),
    (3, 1, '加油！');

🔗 INNER JOIN（内连接）

INNER JOIN返回两个表中匹配的记录。

基本语法

SELECT 列名
FROM 表1
INNER JOIN 表2 ON 表1.列 = 表2.列;

实战例子

-- 查询文章及其作者信息
SELECT 
    posts.title,
    posts.likes,
    users.username,
    users.email
FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- 结果：
-- title            | likes | username | email
-- -----------------+-------+----------+-------------------
-- PostgreSQL入门   | 100   | 长安     | changan@email.com
-- SQL高级技巧      | 50    | 长安     | changan@email.com
-- 我的学习笔记     | 30    | 小明     | xiaoming@email.com
-- 编程心得         | 20    | 小红     | xiaohong@email.com

使用表别名（推荐）

-- 使用别名让SQL更简洁
SELECT 
    p.title,
    p.likes,
    u.username,
    u.email
FROM posts p
INNER JOIN users u ON p.user_id = u.id;

-- 多个JOIN
SELECT 
    p.title AS 文章标题,
    u.username AS 作者,
    c.content AS 评论内容,
    cu.username AS 评论者
FROM posts p
INNER JOIN users u ON p.user_id = u.id
INNER JOIN comments c ON c.post_id = p.id
INNER JOIN users cu ON c.user_id = cu.id;

长安提醒：养成用别名的习惯，代码更清晰！

⬅️ LEFT JOIN（左连接）

LEFT JOIN返回左表所有记录，右表匹配的记录，不匹配则为NULL。

图示理解

左表（users）            右表（posts）
┌────┬────────┐         ┌─────────┬────────┐
│ id │ name   │         │ user_id │ title  │
├────┼────────┤         ├─────────┼────────┤
│ 1  │ 长安   │ ←─┐     │ 1       │ 文章1  │
│ 2  │ 小明   │ ←─┼────→│ 2       │ 文章2  │
│ 3  │ 小红   │ ←─┘     └─────────┴────────┘
│ 4  │ 小李   │ ← （没有匹配，但仍然返回）
└────┴────────┘

实战例子

-- 查询所有用户及其文章（包括没发文章的用户）
SELECT 
    u.username,
    p.title,
    p.likes
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
ORDER BY u.id;

-- 结果：
-- username | title           | likes
-- ---------+-----------------+-------
-- 长安     | PostgreSQL入门  | 100
-- 长安     | SQL高级技巧     | 50
-- 小明     | 我的学习笔记    | 30
-- 小红     | 编程心得        | 20
-- 小李     | NULL            | NULL  ← 小李没有文章

-- 找出没有发过文章的用户
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;

-- 结果：小李

使用场景：

  • 查找"没有XXX"的记录
  • 展示所有主表数据，不管是否有关联

➡️ RIGHT JOIN（右连接）

RIGHT JOIN返回右表所有记录，左表匹配的记录，不匹配则为NULL。

-- 查询所有文章及其作者（即使作者不存在）
SELECT 
    u.username,
    p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;

-- 等价于（一般我们习惯用LEFT JOIN）
SELECT 
    u.username,
    p.title
FROM posts p
LEFT JOIN users u ON p.user_id = u.id;

长安建议：实际工作中，LEFT JOIN用得多，RIGHT JOIN用得少。记住LEFT JOIN就够了！

🔄 FULL OUTER JOIN（全外连接）

返回两个表的所有记录，匹配就关联，不匹配就NULL。

-- 查询所有用户和所有文章的关系
SELECT 
    u.username,
    p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

-- 结果包括：
-- 1. 有文章的用户
-- 2. 没文章的用户（文章为NULL）
-- 3. 没作者的文章（用户为NULL）- 如果有的话

使用场景：比较少见，一般用在数据对比场景。

❌ CROSS JOIN（交叉连接）

返回两个表的笛卡尔积（所有可能的组合）。

-- 每个用户 × 每篇文章
SELECT 
    u.username,
    p.title
FROM users u
CROSS JOIN posts p;

-- 4个用户 × 4篇文章 = 16条记录

长安警告：CROSS JOIN很危险！两个大表做交叉连接会产生天量数据！一般不会用到。

🎯 JOIN的使用技巧

技巧1：多表JOIN

-- 查询文章、作者、评论、评论者
SELECT 
    p.title AS 文章,
    u1.username AS 作者,
    c.content AS 评论,
    u2.username AS 评论者,
    c.created_at AS 评论时间
FROM posts p
INNER JOIN users u1 ON p.user_id = u1.id  -- 关联作者
INNER JOIN comments c ON c.post_id = p.id  -- 关联评论
INNER JOIN users u2 ON c.user_id = u2.id   -- 关联评论者
ORDER BY p.id, c.created_at;

技巧2：自连接（同一个表JOIN自己）

-- 创建员工表（包含上级ID）
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees VALUES 
    (1, '老板', NULL),
    (2, '经理A', 1),
    (3, '经理B', 1),
    (4, '员工A', 2),
    (5, '员工B', 2);

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

-- 结果：
-- 员工   | 上级
-- -------+--------
-- 老板   | NULL
-- 经理A  | 老板
-- 经理B  | 老板
-- 员工A  | 经理A
-- 员工B  | 经理A

技巧3：多条件JOIN

-- JOIN可以有多个条件
SELECT 
    p.title,
    c.content
FROM posts p
INNER JOIN comments c 
    ON c.post_id = p.id 
    AND c.user_id != p.user_id  -- 排除作者自己的评论
    AND c.created_at > p.created_at;  -- 评论时间晚于发布时间

技巧4：使用WHERE进一步过滤

-- JOIN后再用WHERE过滤
SELECT 
    u.username,
    p.title,
    p.likes
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.likes > 40
ORDER BY p.likes DESC;

技巧5：聚合统计

-- 统计每个用户的文章数和总点赞数
SELECT 
    u.username,
    COUNT(p.id) AS 文章数,
    SUM(p.likes) AS 总点赞,
    AVG(p.likes) AS 平均点赞
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY 总点赞 DESC;

-- 统计每篇文章的评论数
SELECT 
    p.title,
    COUNT(c.id) AS 评论数
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, p.title
ORDER BY 评论数 DESC;

📊 实战案例：电商系统

准备数据

-- 商品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    category VARCHAR(50)
);

-- 订单表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total_amount DECIMAL(10, 2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单详情表
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT,
    price DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO products (name, price, category) VALUES 
    ('iPhone 15', 5999.00, '手机'),
    ('MacBook Pro', 12999.00, '电脑'),
    ('AirPods', 1299.00, '耳机'),
    ('iPad', 3999.00, '平板');

INSERT INTO orders (user_id, total_amount, status) VALUES 
    (1, 7298.00, 'completed'),
    (2, 12999.00, 'completed'),
    (1, 5998.00, 'pending');

INSERT INTO order_items (order_id, product_id, quantity, price) VALUES 
    (1, 1, 1, 5999.00),
    (1, 3, 1, 1299.00),
    (2, 2, 1, 12999.00),
    (3, 4, 1, 3999.00),
    (3, 3, 1, 1299.00);

各种查询

-- 1. 查询订单详情（用户、商品、数量）
SELECT 
    o.id AS 订单号,
    u.username AS 用户,
    p.name AS 商品,
    oi.quantity AS 数量,
    oi.price AS 单价,
    oi.quantity * oi.price AS 小计
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
ORDER BY o.id;

-- 2. 统计每个用户的订单总额
SELECT 
    u.username,
    COUNT(o.id) AS 订单数,
    SUM(o.total_amount) AS 总消费,
    AVG(o.total_amount) AS 平均订单金额
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY 总消费 DESC;

-- 3. 统计每个商品的销量
SELECT 
    p.name,
    p.category,
    SUM(oi.quantity) AS 销量,
    SUM(oi.quantity * oi.price) AS 销售额
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category
ORDER BY 销量 DESC;

-- 4. 找出买过iPhone的用户
SELECT DISTINCT u.username
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE p.name LIKE '%iPhone%';

-- 5. 找出没有下过单的用户
SELECT u.username
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

💡 JOIN性能优化技巧

1. 只查询需要的列

-- ❌ 不好
SELECT * 
FROM users u
JOIN posts p ON u.id = p.user_id;

-- ✅ 好
SELECT u.username, p.title 
FROM users u
JOIN posts p ON u.id = p.user_id;

2. 在JOIN列上创建索引

-- 为外键创建索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);

3. 小表在前，大表在后

-- 如果users表小，posts表大
SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id;

4. 先过滤再JOIN

-- ✅ 好：先过滤再JOIN
SELECT u.username, p.title
FROM users u
JOIN (
    SELECT * FROM posts WHERE likes > 100
) p ON u.id = p.user_id;

-- 等价于（PostgreSQL会自动优化）
SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.likes > 100;

🎮 练习题

任务：学生选课系统

创建学生、课程、选课表，完成以下查询：

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    credits INT
);

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    score DECIMAL(5, 2)
);

INSERT INTO students VALUES (1, '张三'), (2, '李四'), (3, '王五');
INSERT INTO courses VALUES (1, '数据库', 3), (2, '算法', 4), (3, '网络', 2);
INSERT INTO enrollments VALUES 
    (1, 1, 1, 85.5),
    (2, 1, 2, 90.0),
    (3, 2, 1, 78.0),
    (4, 3, 3, 88.0);

练习题：

  1. 查询每个学生选了哪些课
  2. 查询每门课有哪些学生选修
  3. 统计每个学生的总学分和平均成绩
  4. 找出没有选课的学生
  5. 找出没有学生选的课程
点击查看答案
-- 1. 每个学生选了哪些课
SELECT s.name, c.name, e.score
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

-- 2. 每门课有哪些学生
SELECT c.name AS 课程, s.name AS 学生, e.score AS 成绩
FROM courses c
JOIN enrollments e ON c.id = e.course_id
JOIN students s ON e.student_id = s.id;

-- 3. 每个学生的总学分和平均成绩
SELECT 
    s.name,
    SUM(c.credits) AS 总学分,
    AVG(e.score) AS 平均成绩
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id
GROUP BY s.id, s.name;

-- 4. 没有选课的学生
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.id IS NULL;

-- 5. 没有学生选的课程
SELECT c.name
FROM courses c
LEFT JOIN enrollments e ON c.id = e.course_id
WHERE e.id IS NULL;

📚 下一步

恭喜！你已经掌握了JOIN查询！现在你可以：

  • ✅ 使用INNER JOIN关联表
  • ✅ 使用LEFT JOIN查找"没有XXX"
  • ✅ 多表JOIN
  • ✅ 自连接
  • ✅ JOIN配合聚合统计

接下来，让我们学习聚合函数，进行更强大的数据统计！

长安的总结：

JOIN是SQL中最重要的技能之一！记住几个要点：

  1. INNER JOIN：只要匹配的
  2. LEFT JOIN：左表全要，右表匹配
  3. 多表JOIN：一步步JOIN，别怕
  4. 别忘索引：外键列一定要加索引

多练习，JOIN用多了就成为本能了！💪

有问题来编程指南找我！

最近更新: