SQLite多表查询 #

-- JOIN 用于根据相关列组合两个或多个表的行

-- 示例表结构
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total REAL,
    order_date DATE
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

INSERT INTO users VALUES (1, 'John', 'john@example.com');
INSERT INTO users VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO users VALUES (3, 'Bob', 'bob@example.com');

INSERT INTO orders VALUES (1, 1, 100.00, '2024-01-01');
INSERT INTO orders VALUES (2, 1, 200.00, '2024-01-15');
INSERT INTO orders VALUES (3, 2, 150.00, '2024-01-20');

INSERT INTO order_items VALUES (1, 1, 'Product A', 2, 50.00);
INSERT INTO order_items VALUES (2, 2, 'Product B', 1, 200.00);
INSERT INTO order_items VALUES (3, 3, 'Product C', 3, 50.00);

┌─────────────┬──────────────────────────────────────┐
│ JOIN类型    │ 说明                                 │
├─────────────┼──────────────────────────────────────┤
│ INNER JOIN  │ 只返回匹配的行                       │
│ LEFT JOIN   │ 返回左表所有行，右表不匹配则为NULL   │
│ CROSS JOIN  │ 笛卡尔积                             │
│ (RIGHT JOIN │ SQLite 3.39+ 支持)                  │
│ (FULL JOIN  │ SQLite 3.39+ 支持)                  │
└─────────────┴──────────────────────────────────────┘

-- INNER JOIN：只返回两个表中匹配的行
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

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

-- 输出：
-- name | order_id | total
-- -----|----------|-------
-- John | 1        | 100.00
-- John | 2        | 200.00
-- Jane | 3        | 150.00
-- Bob 没有订单，所以不出现

-- 连接多个表
SELECT 
    users.name,
    orders.id AS order_id,
    order_items.product_name,
    order_items.quantity
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id;

-- 输出：
-- name | order_id | product_name | quantity
-- -----|----------|--------------|----------
-- John | 1        | Product A    | 2
-- John | 2        | Product B    | 1
-- Jane | 3        | Product C    | 3

-- 使用别名简化
SELECT 
    u.name,
    o.id AS order_id,
    oi.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id;

-- 在JOIN后使用WHERE过滤
SELECT 
    u.name,
    o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

-- 在JOIN条件中使用AND
SELECT 
    u.name,
    o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id AND o.total > 100;

-- LEFT JOIN：返回左表所有行，右表不匹配则为NULL
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- 示例
SELECT 
    u.name,
    o.id AS order_id,
    o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 输出：
-- name | order_id | total
-- -----|----------|-------
-- John | 1        | 100.00
-- John | 2        | 200.00
-- Jane | 3        | 150.00
-- Bob  | NULL     | NULL   -- Bob没有订单，但仍然出现

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

-- 输出：
-- name
-- -----
-- Bob

-- 统计每个用户的订单数（包括0订单的用户）
SELECT 
    u.name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 输出：
-- name | order_count
-- -----|------------
-- John | 2
-- Jane | 1
-- Bob  | 0

-- RIGHT JOIN：返回右表所有行，左表不匹配则为NULL
SELECT 
    u.name,
    o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 变通方法（使用LEFT JOIN）
SELECT 
    u.name,
    o.id AS order_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

-- FULL JOIN：返回两个表的所有行
SELECT 
    u.name,
    o.id AS order_id
FROM users u
FULL JOIN orders o ON u.id = o.user_id;

-- 变通方法（使用UNION）
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION ALL
SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL;

-- CROSS JOIN：返回两个表的笛卡尔积
SELECT 
    u.name,
    p.product_name
FROM users u
CROSS JOIN products p;

-- 如果users有3行，products有5行
-- 结果有 3 * 5 = 15 行

-- 生成所有组合
-- 例如：生成所有用户和所有日期的组合
SELECT 
    u.name,
    d.date
FROM users u
CROSS JOIN (
    SELECT date('2024-01-01') AS date
    UNION SELECT date('2024-01-02')
    UNION SELECT date('2024-01-03')
) d;

-- 自连接：表与自己连接
-- 示例：员工和经理
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER
);

INSERT INTO employees VALUES (1, 'CEO', NULL);
INSERT INTO employees VALUES (2, 'Manager A', 1);
INSERT INTO employees VALUES (3, 'Manager B', 1);
INSERT INTO employees VALUES (4, 'Employee 1', 2);
INSERT INTO employees VALUES (5, 'Employee 2', 3);

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

-- 输出：
-- employee   | manager
-- -----------|---------
-- CEO        | NULL
-- Manager A  | CEO
-- Manager B  | CEO
-- Employee 1 | Manager A
-- Employee 2 | Manager B

-- 使用自连接查找重复
SELECT a.name, a.email
FROM users a
JOIN users b ON a.email = b.email AND a.id > b.id;

-- 比较同一表中的不同行
SELECT 
    a.name AS user1,
    b.name AS user2,
    a.salary - b.salary AS salary_diff
FROM employees a
JOIN employees b ON a.department_id = b.department_id AND a.id < b.id;

-- 为JOIN列创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- 查询会使用索引
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

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

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

-- 避免不必要的JOIN
-- 如果只需要用户信息，不要JOIN订单表
SELECT name FROM users WHERE id = 1;

-- 避免过多的JOIN
-- 不推荐：5个以上表的JOIN

-- 替代方案：使用子查询或CTE
WITH user_orders AS (
    SELECT user_id, SUM(total) AS total
    FROM orders
    GROUP BY user_id
)
SELECT u.name, uo.total
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- 统计每个用户的订单数和总金额
SELECT 
    u.name,
    COUNT(DISTINCT o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 输出：
-- name | order_count | total_amount
-- -----|-------------|-------------
-- John | 2           | 300.00
-- Jane | 1           | 150.00
-- Bob  | 0           | 0.00

-- 查询员工层级
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
    printf('%*s%s', (level - 1) * 2, '', name) AS hierarchy
FROM employee_hierarchy;

-- 每个用户的最近订单
WITH latest_orders AS (
    SELECT 
        user_id,
        id,
        total,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT 
    u.name,
    lo.total,
    lo.order_date
FROM users u
LEFT JOIN latest_orders lo ON u.id = lo.user_id AND lo.rn = 1;

-- JOIN条件中的NULL
-- NULL = NULL 不匹配

-- 使用COALESCE处理
SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON COALESCE(t1.col, '') = COALESCE(t2.col, '');

-- 避免列名冲突
-- 错误：两个表都有id列
SELECT id FROM users JOIN orders ON ...;

-- 正确：使用表别名
SELECT u.id AS user_id, o.id AS order_id
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 避免在JOIN条件中使用函数
-- 不推荐
SELECT * FROM users u
JOIN orders o ON lower(u.email) = lower(o.email);

-- 推荐
SELECT * FROM users u
JOIN orders o ON u.email = o.email COLLATE NOCASE;