MariaDB多表查询 #

JOIN类型
├── INNER JOIN（内连接）
│   └── 只返回匹配的记录
├── LEFT JOIN（左连接）
│   └── 返回左表所有记录
├── RIGHT JOIN（右连接）
│   └── 返回右表所有记录
├── CROSS JOIN（交叉连接）
│   └── 笛卡尔积
└── NATURAL JOIN（自然连接）
    └── 自动匹配同名列

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    department_id INT
);

INSERT INTO users (name, email, department_id) VALUES
    ('John', 'john@example.com', 1),
    ('Jane', 'jane@example.com', 1),
    ('Bob', 'bob@example.com', 2),
    ('Alice', 'alice@example.com', NULL);

-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

INSERT INTO departments (name) VALUES
    ('Engineering'),
    ('Marketing'),
    ('Sales');

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    product_name VARCHAR(100),
    amount DECIMAL(10,2),
    order_date DATE
);

INSERT INTO orders (user_id, product_name, amount, order_date) VALUES
    (1, 'Laptop', 1000.00, '2024-01-15'),
    (1, 'Mouse', 50.00, '2024-01-16'),
    (2, 'Keyboard', 100.00, '2024-01-17'),
    (3, 'Monitor', 300.00, '2024-01-18');

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

-- 查询用户及其部门
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u
INNER JOIN departments d ON u.department_id = d.id;

+-----------+----------------+
| user_name | department_name|
+-----------+----------------+
| John      | Engineering    |
| Jane      | Engineering    |
| Bob       | Marketing      |
+-----------+----------------+

-- 注意：Alice没有部门，不会出现在结果中

-- 连接三个表
SELECT 
    u.name AS user_name,
    d.name AS department_name,
    o.product_name,
    o.amount
FROM users u
INNER JOIN departments d ON u.department_id = d.id
INNER JOIN orders o ON u.id = o.user_id;

+-----------+----------------+--------------+--------+
| user_name | department_name| product_name | amount |
+-----------+----------------+--------------+--------+
| John      | Engineering    | Laptop       | 1000.00|
| John      | Engineering    | Mouse        |   50.00|
| Jane      | Engineering    | Keyboard     |  100.00|
| Bob       | Marketing      | Monitor      |  300.00|
+-----------+----------------+--------------+--------+

-- 当连接列名相同时，可以使用USING
SELECT u.name, d.name
FROM users u
INNER JOIN departments d USING (id);

-- 注意：这里id不是外键，仅作语法演示

-- 旧式语法（不推荐）
SELECT u.name, d.name
FROM users u, departments d
WHERE u.department_id = d.id;

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- 查询所有用户及其部门（包括没有部门的用户）
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

+-----------+----------------+
| user_name | department_name|
+-----------+----------------+
| John      | Engineering    |
| Jane      | Engineering    |
| Bob       | Marketing      |
| Alice     | NULL           |  -- Alice没有部门
+-----------+----------------+

-- 查询没有部门的用户
SELECT u.name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
WHERE d.id IS NULL;

+-------+
| name  |
+-------+
| Alice |
+-------+

-- 查询所有用户及其订单（包括没有订单的用户）
SELECT 
    u.name AS user_name,
    o.product_name,
    o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

+-----------+--------------+--------+
| user_name | product_name | amount |
+-----------+--------------+--------+
| John      | Laptop       | 1000.00|
| John      | Mouse        |   50.00|
| Jane      | Keyboard     |  100.00|
| Bob       | Monitor      |  300.00|
| Alice     | NULL         |   NULL |  -- 没有订单
+-----------+--------------+--------+

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- 查询所有部门及其用户（包括没有用户的部门）
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u
RIGHT JOIN departments d ON u.department_id = d.id;

+-----------+----------------+
| user_name | department_name|
+-----------+----------------+
| John      | Engineering    |
| Jane      | Engineering    |
| Bob       | Marketing      |
| NULL      | Sales          |  -- Sales部门没有用户
+-----------+----------------+

-- RIGHT JOIN可以转换为LEFT JOIN
-- 以下两个查询等价：
SELECT u.name, d.name FROM users u RIGHT JOIN departments d ON u.department_id = d.id;
SELECT u.name, d.name FROM departments d LEFT JOIN users u ON u.department_id = d.id;

-- 推荐使用LEFT JOIN，更直观

SELECT columns
FROM table1
CROSS JOIN table2;

-- 生成所有组合（笛卡尔积）
SELECT u.name AS user_name, d.name AS department_name
FROM users u
CROSS JOIN departments d;

+-----------+----------------+
| user_name | department_name|
+-----------+----------------+
| John      | Engineering    |
| John      | Marketing      |
| John      | Sales          |
| Jane      | Engineering    |
| Jane      | Marketing      |
| Jane      | Sales          |
| Bob       | Engineering    |
| Bob       | Marketing      |
| Bob       | Sales          |
| Alice     | Engineering    |
| Alice     | Marketing      |
| Alice     | Sales          |
+-----------+----------------+
-- 4用户 × 3部门 = 12条记录

-- 生成日期和产品的所有组合
SELECT d.date, p.product_name
FROM dates d
CROSS JOIN products p;

-- 生成报表模板
SELECT u.user_id, m.month
FROM users u
CROSS JOIN months m;

-- 自连接：表与自身连接
-- 用于处理层次结构或比较同一表中的记录

-- 查找同一部门的其他员工
SELECT 
    e1.name AS employee,
    e2.name AS colleague
FROM users e1
INNER JOIN users e2 ON e1.department_id = e2.department_id AND e1.id < e2.id;

+----------+----------+
| employee | colleague|
+----------+----------+
| John     | Jane     |  -- 同属Engineering
+----------+----------+

-- 员工表包含经理ID
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
    (1, 'CEO', NULL),
    (2, 'VP1', 1),
    (3, 'VP2', 1),
    (4, 'Manager1', 2),
    (5, 'Manager2', 2);

-- 查询员工及其经理
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    |
| VP1      | CEO     |
| VP2      | CEO     |
| Manager1 | VP1     |
| Manager2 | VP1     |
+----------+---------+

-- 自动匹配同名列
SELECT columns
FROM table1
NATURAL JOIN table2;

-- 自动使用同名列连接
SELECT * FROM users NATURAL JOIN departments;

-- 等价于
SELECT * FROM users u INNER JOIN departments d ON u.id = d.id;

-- 注意：不推荐使用，容易出错

-- 连接多个表
SELECT 
    u.name AS user_name,
    d.name AS department_name,
    o.product_name,
    o.amount
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100 OR o.amount IS NULL;

-- 连接派生表
SELECT 
    u.name,
    o.total_amount
FROM users u
LEFT JOIN (
    SELECT user_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

-- 使用CTE简化复杂连接
WITH user_orders AS (
    SELECT 
        user_id,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
)
SELECT 
    u.name,
    d.name AS department,
    uo.order_count,
    uo.total_amount
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- 为连接列创建索引
CREATE INDEX idx_users_department ON users(department_id);
CREATE INDEX idx_orders_user ON orders(user_id);

-- 外键自动创建索引
ALTER TABLE orders ADD CONSTRAINT fk_user 
FOREIGN KEY (user_id) REFERENCES users(id);

-- 小表驱动大表
-- 将过滤后结果集较小的表放在前面

-- 查看执行计划
EXPLAIN SELECT u.name, o.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 使用STRAIGHT_JOIN强制连接顺序
SELECT STRAIGHT_JOIN u.name, o.product_name
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

-- 错误：忘记连接条件
SELECT u.name, d.name FROM users u, departments d;  -- 笛卡尔积！

-- 正确：添加连接条件
SELECT u.name, d.name 
FROM users u
INNER JOIN departments d ON u.department_id = d.id;

-- 使用IN（可能性能差）
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders);

-- 使用EXISTS（通常更好）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 使用JOIN（适合需要返回多列）
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- INNER JOIN vs LEFT JOIN
-- INNER JOIN通常更快（过滤掉不匹配的记录）

-- 但如果需要所有记录，必须使用LEFT JOIN
SELECT u.name, o.product_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 问题：一对多关系导致重复
SELECT u.name, o.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- John出现两次（有两个订单）

-- 解决方案1：使用聚合
SELECT u.name, GROUP_CONCAT(o.product_name) AS products
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- 解决方案2：使用子查询
SELECT u.name, 
       (SELECT GROUP_CONCAT(product_name) FROM orders WHERE user_id = u.id) AS products
FROM users u;

-- LEFT JOIN产生NULL值
SELECT u.name, d.name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

-- 处理NULL值
SELECT u.name, COALESCE(d.name, 'No Department') AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

-- 错误：连接条件不正确
SELECT u.name, d.name
FROM users u
INNER JOIN departments d ON u.id = d.id;  -- 错误的连接条件

-- 正确：使用外键
SELECT u.name, d.name
FROM users u
INNER JOIN departments d ON u.department_id = d.id;

-- 查询每个用户的订单统计
SELECT 
    u.name AS user_name,
    d.name AS department,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_amount,
    COALESCE(AVG(o.amount), 0) AS avg_amount
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, d.name
ORDER BY total_amount DESC;

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

-- 查找没有用户的部门
SELECT d.name
FROM departments d
LEFT JOIN users u ON d.id = u.department_id
WHERE u.id IS NULL;

-- 查找购买过相同产品的用户对
SELECT DISTINCT 
    o1.user_id AS user1,
    o2.user_id AS user2,
    o1.product_name
FROM orders o1
INNER JOIN orders o2 ON o1.product_name = o2.product_name AND o1.user_id < o2.user_id;