SQL Server多表查询 #

SQL Server JOIN类型
├── INNER JOIN    → 内连接（交集）
├── LEFT JOIN     → 左外连接
├── RIGHT JOIN    → 右外连接
├── FULL JOIN     → 全外连接
├── CROSS JOIN    → 交叉连接（笛卡尔积）
└── SELF JOIN     → 自连接

-- 创建示例表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    location NVARCHAR(50)
);

INSERT INTO departments VALUES
    (1, 'IT', 'Beijing'),
    (2, 'HR', 'Shanghai'),
    (3, 'Finance', 'Guangzhou'),
    (4, 'Marketing', NULL);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    department_id INT,
    salary DECIMAL(10,2),
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

INSERT INTO users VALUES
    (1, 'John', 1, 5000.00, NULL),
    (2, 'Jane', 1, 6000.00, 1),
    (3, 'Bob', 2, 4500.00, NULL),
    (4, 'Alice', 2, 4000.00, 3),
    (5, 'Charlie', NULL, 7000.00, NULL),
    (6, 'David', 1, 5500.00, 1);

CREATE TABLE projects (
    id INT PRIMARY KEY,
    name NVARCHAR(50),
    department_id INT
);

INSERT INTO projects VALUES
    (1, 'Project A', 1),
    (2, 'Project B', 1),
    (3, 'Project C', 2),
    (4, 'Project D', 5);

-- 内连接：只返回匹配的行
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      | IT
-- Jane      | IT
-- Bob       | HR
-- Alice     | HR
-- David     | IT
-- 注意：Charlie没有部门，不会出现

-- 连接多个表
SELECT 
    u.name AS user_name,
    d.name AS department_name,
    p.name AS project_name
FROM users u
INNER JOIN departments d ON u.department_id = d.id
INNER JOIN projects p ON d.id = p.department_id;

-- 使用WHERE替代ON
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u, departments d
WHERE u.department_id = d.id;

-- 多条件连接
SELECT 
    u.name,
    d.name AS department
FROM users u
INNER JOIN departments d 
    ON u.department_id = d.id
    AND d.location = 'Beijing';

-- 使用额外条件
SELECT 
    u.name,
    d.name AS department
FROM users u
INNER JOIN departments d ON u.department_id = d.id
WHERE u.salary > 5000;

-- 左连接：返回左表所有行，右表无匹配则为NULL
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      | IT
-- Jane      | IT
-- Bob       | HR
-- Alice     | HR
-- Charlie   | NULL           ← 没有部门
-- David     | IT

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

-- 结果：Charlie

-- 注意：WHERE和ON的区别
-- 错误：这会变成INNER JOIN
SELECT u.name, d.name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
WHERE d.location = 'Beijing';  -- 过滤掉NULL

-- 正确：保留左表所有行
SELECT u.name, d.name
FROM users u
LEFT JOIN departments d 
    ON u.department_id = d.id 
    AND d.location = 'Beijing';

-- 多表左连接
SELECT 
    u.name AS user_name,
    d.name AS department_name,
    p.name AS project_name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
LEFT JOIN projects p ON d.id = p.department_id;

-- 右连接：返回右表所有行，左表无匹配则为NULL
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      | IT
-- Jane      | IT
-- David     | IT
-- Bob       | HR
-- Alice     | HR
-- NULL      | Finance        ← 没有员工
-- NULL      | Marketing      ← 没有员工

-- 找出没有员工的部门
SELECT d.name AS department_name
FROM users u
RIGHT JOIN departments d ON u.department_id = d.id
WHERE u.id IS NULL;

-- 结果：Finance, Marketing

-- 全连接：返回两表所有行
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u
FULL JOIN departments d ON u.department_id = d.id;

-- 结果
-- user_name | department_name
-- ----------|----------------
-- John      | IT
-- Jane      | IT
-- David     | IT
-- Bob       | HR
-- Alice     | HR
-- Charlie   | NULL           ← 没有部门
-- NULL      | Finance        ← 没有员工
-- NULL      | Marketing      ← 没有员工

-- 找出不匹配的行（任一侧）
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u
FULL JOIN departments d ON u.department_id = d.id
WHERE u.id IS NULL OR d.id IS NULL;

-- 结果
-- user_name | department_name
-- ----------|----------------
-- Charlie   | NULL
-- NULL      | Finance
-- NULL      | Marketing

-- 交叉连接：笛卡尔积
SELECT 
    u.name AS user_name,
    d.name AS department_name
FROM users u
CROSS JOIN departments d;

-- 结果：6 users × 4 departments = 24 行

-- 等价于
SELECT u.name, d.name
FROM users u, departments d;

-- 生成所有组合（如：每个用户每月的指标）
WITH months AS (
    SELECT 1 AS month UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
)
SELECT 
    u.name,
    m.month
FROM users u
CROSS JOIN months m
ORDER BY u.name, m.month;

-- 自连接：员工和经理
SELECT 
    e.name AS employee,
    m.name AS manager
FROM users e
LEFT JOIN users m ON e.manager_id = m.id;

-- 结果
-- employee | manager
-- ---------|--------
-- John     | NULL
-- Jane     | John
-- Bob      | NULL
-- Alice    | Bob
-- Charlie  | NULL
-- David    | John

-- 查找同事（同一部门）
SELECT 
    u1.name AS employee1,
    u2.name AS employee2,
    d.name AS department
FROM users u1
INNER JOIN users u2 ON u1.department_id = u2.department_id AND u1.id < u2.id
INNER JOIN departments d ON u1.department_id = d.id;

-- 结果
-- employee1 | employee2 | department
-- ----------|-----------|------------
-- John      | Jane      | IT
-- John      | David     | IT
-- Jane      | David     | IT
-- Bob       | Alice     | HR

-- 复合键连接
SELECT *
FROM order_items oi
INNER JOIN products p 
    ON oi.product_id = p.id
    AND oi.warehouse_id = p.warehouse_id;

-- 非等值连接
SELECT 
    u.name,
    u.salary,
    g.grade
FROM users u
INNER JOIN salary_grades g 
    ON u.salary BETWEEN g.min_salary AND g.max_salary;

-- SQL Server不支持USING，使用ON代替
-- MySQL/PostgreSQL:
-- SELECT * FROM users JOIN departments USING (department_id)

-- SQL Server:
SELECT * FROM users u
JOIN departments d ON u.department_id = d.department_id;

-- CROSS APPLY：类似INNER JOIN
-- 为每一行应用表值函数或子查询
SELECT 
    u.name,
    t.top_order_date,
    t.top_amount
FROM users u
CROSS APPLY (
    SELECT TOP 1 
        order_date AS top_order_date,
        amount AS top_amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
) t;

-- 只返回有订单的用户

-- OUTER APPLY：类似LEFT JOIN
SELECT 
    u.name,
    t.top_order_date,
    t.top_amount
FROM users u
OUTER APPLY (
    SELECT TOP 1 
        order_date AS top_order_date,
        amount AS top_amount
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
) t;

-- 返回所有用户，没有订单的显示NULL

-- CROSS APPLY可以做到JOIN做不到的事情
-- 例如：为每行取TOP N

-- 每个部门工资最高的2人
SELECT 
    d.name AS department,
    t.name AS employee,
    t.salary
FROM departments d
CROSS APPLY (
    SELECT TOP 2 u.name, u.salary
    FROM users u
    WHERE u.department_id = d.id
    ORDER BY u.salary DESC
) t;

-- 使用JOIN无法直接实现

-- SQL Server优化器会自动选择最佳顺序
-- 但可以提供提示

-- 使用OPTION提示
SELECT *
FROM users u
INNER JOIN departments d ON u.department_id = d.id
OPTION (FORCE ORDER);  -- 强制按书写顺序连接

-- 为连接列创建索引
CREATE INDEX ix_users_department_id ON users(department_id);

-- 覆盖索引
CREATE INDEX ix_users_department_cover ON users(department_id) 
INCLUDE (name, salary);

-- LOOP JOIN：嵌套循环连接
SELECT * FROM users u
INNER LOOP JOIN departments d ON u.department_id = d.id;

-- HASH JOIN：哈希连接
SELECT * FROM users u
INNER HASH JOIN departments d ON u.department_id = d.id;

-- MERGE JOIN：合并连接
SELECT * FROM users u
INNER MERGE JOIN departments d ON u.department_id = d.id;

-- 错误：缺少连接条件
SELECT * FROM users, departments;  -- 笛卡尔积

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

-- 统计每个部门的员工数和项目数
SELECT 
    d.name AS department,
    COUNT(DISTINCT u.id) AS employee_count,
    COUNT(DISTINCT p.id) AS project_count
FROM departments d
LEFT JOIN users u ON d.id = u.department_id
LEFT JOIN projects p ON d.id = p.department_id
GROUP BY d.id, d.name;

-- 使用CTE实现层级查询
WITH EmployeeHierarchy AS (
    -- 锚点：顶级员工
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS NVARCHAR(1000)) AS path
    FROM users
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下属员工
    SELECT 
        u.id,
        u.name,
        u.manager_id,
        eh.level + 1,
        CAST(eh.path + ' > ' + u.name AS NVARCHAR(1000))
    FROM users u
    INNER JOIN EmployeeHierarchy eh ON u.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy
ORDER BY level, path;

-- 根据条件选择不同的连接
SELECT 
    u.name,
    CASE 
        WHEN u.department_id IS NOT NULL THEN d.name
        ELSE 'No Department'
    END AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

连接类型选择：
├── 需要两表匹配的数据 → INNER JOIN
├── 需要左表所有数据 → LEFT JOIN
├── 需要右表所有数据 → RIGHT JOIN
├── 需要两表所有数据 → FULL JOIN
├── 需要所有组合 → CROSS JOIN
├── 同表层级查询 → SELF JOIN
└── 每行需要TOP N → CROSS APPLY

性能优化：
├── 连接列创建索引
├── 小表驱动大表
├── 避免SELECT *
├── 使用覆盖索引
└── 适当使用连接提示