连接查询入门
连接查询入门
想象你去银行办业务，柜员需要同时查看你的"账户信息"和"个人信息"两张表。她不会分别查两次再人工对照，而是通过你的身份证号把两张表"连接"起来，一次性看到完整画面。JOIN就是SQL中让多张表"对话"的魔法。
1. 核心概念
为什么需要JOIN？
在设计数据库时，我们会把数据拆分到多张表中以避免冗余。比如employees表只存department_id，而不重复存储部门名称和地址。但查询时经常需要"拼回"完整信息，这时就需要JOIN。
-- 没有JOIN：只能看到department_id，不知道部门名
SELECT name, department_id, salary FROM employees;
-- 有JOIN：员工名和部门名一起展示
SELECT e.name, d.name AS department, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;
表关系类型
| 关系类型 | 说明 | 示例 |
|---|---|---|
| 一对一（1:1） | A表一行对应B表一行 | 一个用户对应一个身份证 |
| 一对多（1:N） | A表一行对应B表多行 | 一个部门有多个员工 |
| 多对多（M:N） | A表多行对应B表多行 | 学生和课程（通过选课表关联） |
在我们的示例数据库中：
departments→employees：一对多（一个部门有多个员工）products→orders：一对多（一个产品可以出现在多个订单中）
外键（Foreign Key）
外键是实现表关系的桥梁。它是A表中引用B表主键的列，确保数据的一致性。
-- employees表中的department_id就是外键，指向departments表的id
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
department_id INTEGER, -- 外键
salary DECIMAL(10,2),
hire_date DATE,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
PRAGMA foreign_keys = ON;才能生效。
INNER JOIN 语法
INNER JOIN（内连接）只返回两张表中都匹配的行。如果某行在另一张表中没有对应数据，则被排除。
SELECT 列名
FROM 表A
INNER JOIN 表B ON 表A.列 = 表B.列;
ON 条件
ON子句指定连接条件——即两张表通过哪一列进行匹配。通常是比较外键和主键。
-- ON 条件：employees.department_id = departments.id
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
表别名（AS）
当表名较长或需要区分同名列时，可以用AS给表起别名，简化书写：
-- 没有别名：每次都要写全名
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
-- 有别名：更简洁
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
2. 基本语法/用法
INNER JOIN 完整语法
SELECT 表A.列1, 表A.列2, 表B.列1
FROM 表A
INNER JOIN 表B ON 表A.外键列 = 表B.主键列
WHERE 条件
ORDER BY 列名;
INNER关键字可以省略，直接写JOIN等同于INNER JOIN。
A JOIN B ON ... JOIN C ON ...。
示例：查询员工及其部门（难度⭐）
最基本的需求——查看每个员工所属的部门名称：
SELECT e.name AS employee, d.name AS department, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
输出：
employee department salary
-------- ---------- --------
张三 技术部 15000.00
李四 技术部 18000.00
王五 市场部 12000.00
赵六 财务部 13000.00
钱七 技术部 20000.00
孙八 市场部 11000.00
吴十 财务部 14000.00
注意：周九没有出现，因为他的department_id是NULL，INNER JOIN只保留两表都匹配的行。
示例：查询订单详情（三表连接）（难度⭐⭐）
实际业务中经常需要连接多张表。查询每个订单的客户名、商品名、单价和金额：
SELECT o.customer_name, p.name AS product,
p.price, o.quantity,
(p.price * o.quantity) AS total
FROM orders o
INNER JOIN products p ON o.product_id = p.id
ORDER BY total DESC;
输出：
customer_name product price quantity total
------------- -------------- -------- -------- --------
小李 iPad Air 4799.00 3 14397.00
小刚 MacBook Pro 12999.00 1 12999.00
小王 iPhone 15 5999.00 2 11998.00
小明 iPhone 15 5999.00 1 5999.00
小刚 iPhone 14 4999.00 1 4999.00
小红 Magic Keyboard 999.00 5 4995.00
小明 Apple Watch 2999.00 1 2999.00
小红 AirPods Pro 1899.00 2 3798.00
3. 常见应用场景
场景一：查询技术部所有员工
先通过JOIN找到部门ID，再用WHERE过滤：
SELECT e.name, e.salary, e.hire_date
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = '技术部';
输出：
name salary hire_date
------ -------- ----------
张三 15000.00 2023-01-15
李四 18000.00 2022-06-01
钱七 20000.00 2020-08-05
场景二：统计每个部门的员工数量
SELECT d.name AS department, COUNT(e.id) AS emp_count
FROM departments d
INNER JOIN employees e ON d.id = e.department_id
GROUP BY d.name;
输出：
department emp_count
---------- ---------
技术部 3
市场部 2
财务部 2
❓ 常见问题
Q：JOIN和子查询哪个性能更好？ A： 一般情况下JOIN性能更优，因为数据库引擎对JOIN有专门的优化器。子查询在某些场景下可能产生临时表，性能稍差。但具体要看数据量和索引情况，建议用
EXPLAIN查看执行计划。
Q：ON和WHERE有什么区别？ A：
ON是连接条件，决定两张表如何配对；WHERE是过滤条件，决定最终显示哪些行。在INNER JOIN中效果类似，但在LEFT JOIN中区别很大——ON不影响左表的保留，WHERE会过滤掉NULL行。
Q：表别名是必须的吗？ A： 不是必须的，但强烈建议使用。表别名让SQL更简洁，尤其在多表连接时能大幅提高可读性。
Q：如果外键为NULL会怎样？ A： INNER JOIN会排除该行，因为没有匹配的关联数据。如果想保留这些行，需要用LEFT JOIN（下一课会讲）。
📖 小节
- 数据库通过拆分表来避免数据冗余，JOIN用于在查询时将多张表"拼回"完整信息
- 表关系有三种：一对一、一对多、多对多，最常见的是一对多
- 外键是表关系的桥梁，引用另一张表的主键
- INNER JOIN只返回两表都匹配的行，用
ON指定连接条件 - 表别名（
AS）让SQL更简洁，多表连接时建议始终使用
📝 作业
作业1（⭐）：编写查询，显示所有订单的客户名、商品名和商品类别（需要连接orders和products表）。
作业2（⭐⭐）：编写查询，显示每个部门的员工平均薪资，只显示平均薪资超过12000的部门（提示：JOIN + GROUP BY + HAVING）。
作业3（⭐⭐⭐）：编写查询，显示每个客户购买的商品总金额，结果按总金额降序排列。要求显示客户名、订单数量和总金额。
下一课
👉 08-join-types - 连接类型详解：深入学习LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN和SELF JOIN，掌握各连接类型的选择策略！
