SQLite Join

SQLite 的 JOIN 用于从多个表中组合数据，基于相关列进行连接。最常用的是 INNER JOIN 和 LEFT JOIN。

---

基本语法

SELECT columns
FROM table1
[INNER] JOIN table2 ON table1.column = table2.column
[LEFT JOIN table3 ON ...]
[WHERE ...]
[ORDER BY ...];

---

JOIN 类型

类型	说明	匹配行
INNER JOIN	仅返回两表都匹配的行	交集
LEFT JOIN	返回左表所有行，右表无匹配时填 NULL	左表 + 匹配
RIGHT JOIN	SQLite 不支持	—
FULL JOIN	SQLite 不支持	—
CROSS JOIN	笛卡尔积（每行配对）	所有组合

---

示例表结构

-- 用户表
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    country TEXT DEFAULT 'SG'
);

-- 订单表
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    product TEXT,
    amount REAL,
    order_date DATE,
    FOREIGN KEY(user_id) REFERENCES users(id)
);

-- 插入数据
INSERT INTO users (name, email, country) VALUES
('Alice', 'alice@sg.com', 'SG'),
('Bob', 'bob@my.com', 'MY'),
('Charlie', 'charlie@sg.com', 'SG'),
('David', NULL, 'SG');  -- 无邮箱

INSERT INTO orders (user_id, product, amount, order_date) VALUES
(1, 'Laptop', 1200, '2025-11-01'),
(1, 'Mouse', 25, '2025-11-02'),
(2, 'Keyboard', 80, '2025-11-03'),
(3, 'Monitor', 350, '2025-11-04'),
(999, 'Webcam', 90, '2025-11-05');  -- user_id 不存在

---

1. INNER JOIN（最常用）

-- 查找有订单的用户及订单详情
SELECT 
    u.name, 
    u.email, 
    o.product, 
    o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

结果：

name    | email         | product   | amount
--------|---------------|-----------|-------
Alice   | alice@sg.com  | Laptop    | 1200
Alice   | alice@sg.com  | Mouse     | 25
Bob     | bob@my.com    | Keyboard  | 80
Charlie | charlie@sg.com| Monitor   | 350

只有两表都匹配的行出现（David 无订单，订单 999 用户不存在）

---

2. LEFT JOIN（保留左表所有行）

-- 查找所有用户，即使没有订单
SELECT 
    u.name, 
    u.email, 
    o.product, 
    o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

结果：

name    | email         | product   | amount
--------|---------------|-----------|-------
Alice   | alice@sg.com  | Laptop    | 1200
Alice   | alice@sg.com  | Mouse     | 25
Bob     | bob@my.com    | Keyboard  | 80
Charlie | charlie@sg.com| Monitor   | 350
David   | NULL          | NULL      | NULL

David 出现，订单字段为 NULL

---

3. 多表 JOIN

-- 假设有 payments 表
CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    order_id INTEGER,
    method TEXT,
    paid_amount REAL
);

INSERT INTO payments VALUES
(1, 1, 'Card', 1200),
(2, 3, 'Bank', 80);

-- 连接 users → orders → payments
SELECT 
    u.name, 
    o.product, 
    p.method, 
    p.paid_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN payments p ON o.order_id = p.order_id;

---

4. JOIN + 聚合函数

-- 每个用户的订单总数和总消费
SELECT 
    u.name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

---

5. CROSS JOIN（笛卡尔积）

-- 所有用户与所有产品组合（少用）
CREATE TABLE products (name TEXT);
INSERT INTO products VALUES ('Laptop'), ('Mouse'), ('Keyboard');

SELECT u.name, p.name AS product
FROM users u
CROSS JOIN products p;

---

6. 自连接（Self-Join）

-- 查找同名用户（假设有重复名字）
SELECT a.name, a.email, b.email AS same_name_email
FROM users a
JOIN users b ON a.name = b.name AND a.id != b.id;

---

性能优化

-- 为 JOIN 字段建索引（强烈推荐！）
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);

---

常见错误

错误	正确做法
JOIN orders WHERE users.id = orders.user_id	必须用 ON
忘记别名导致列名冲突	用 u.、o. 区分
RIGHT JOIN	SQLite 不支持，用 LEFT JOIN 交换表顺序

---

快速参考

需求	JOIN 类型
两表交集	INNER JOIN
保留左表	LEFT JOIN
所有组合	CROSS JOIN
多表链	JOIN ... JOIN ...
统计	LEFT JOIN + GROUP BY

---

最佳实践

1. 始终使用表别名（u, o）
2. 为外键字段建索引
3. 优先用 LEFT JOIN 保留主表
4. 用 EXPLAIN QUERY PLAN 检查是否走索引

---

需要我帮你写一个针对你表的 JOIN 查询吗？
请提供两个表名、关联字段和想显示的列！