412_SQL多表连接策略¶

一句话说明¶

SQL连接就是把多张表按关联条件拼在一起，不同JOIN类型决定了"拼不上的行怎么处理"。

核心知识点¶

JOIN类型一览¶

JOIN类型	保留哪边	典型用途
INNER JOIN	两边都有匹配的行	最常用，取交集
LEFT JOIN	左表全部保留	保留主表，右表可为NULL
RIGHT JOIN	右表全部保留	同LEFT，方向相反
FULL OUTER JOIN	两边都保留	取并集
CROSS JOIN	笛卡尔积	组合所有可能
SELF JOIN	表与自身连接	层级数据、比较同表行

连接算法（数据库内部使用）¶

算法	适用场景	条件
Nested Loop Join	小表连大表（有索引）	驱动表小
Hash Join	大表连大表	等值连接
Merge Join	两表都已排序	等值连接

实战SQL示例¶

-- ========== 生信场景：基因注释数据库 ==========
-- genes(gene_id, gene_name, chromosome, strand)
-- gene_expression(gene_id, sample_id, expression, condition)
-- go_annotations(gene_id, go_id, evidence_code)
-- go_terms(go_id, go_name, namespace)
-- samples(sample_id, patient_id, disease_status, tissue)

-- ========== 1. INNER JOIN：最常用，取有注释且有表达量的基因 ==========
SELECT 
    g.gene_name,
    e.expression,
    e.condition,
    go.go_name
FROM genes g
INNER JOIN gene_expression e ON g.gene_id = e.gene_id    -- 连接表达量
INNER JOIN go_annotations goa ON g.gene_id = goa.gene_id  -- 连接GO注释
INNER JOIN go_terms go ON goa.go_id = go.go_id            -- 连接GO名称
WHERE e.condition = 'treatment'
  AND go.namespace = 'biological_process'
  AND e.expression > 100;
-- 结果：只保留同时有表达量数据且有BP注释的基因

-- ========== 2. LEFT JOIN：保留所有基因（即使没有GO注释） ==========
SELECT 
    g.gene_id,
    g.gene_name,
    COUNT(goa.go_id) AS go_annotation_count,  -- 有注释就计数，没有就是0
    GROUP_CONCAT(go.go_name SEPARATOR '; ') AS go_terms  -- MySQL语法
    -- STRING_AGG(go.go_name, '; ') AS go_terms  -- PostgreSQL语法
FROM genes g
LEFT JOIN go_annotations goa ON g.gene_id = goa.gene_id
LEFT JOIN go_terms go ON goa.go_id = go.go_id
GROUP BY g.gene_id, g.gene_name
ORDER BY go_annotation_count DESC;
-- 结果：所有基因都出现，没有GO注释的基因go_annotation_count=0

-- ========== 3. 用LEFT JOIN找"孤立"数据（反连接） ==========
-- 找没有GO注释的基因（未注释基因）
SELECT g.gene_id, g.gene_name
FROM genes g
LEFT JOIN go_annotations goa ON g.gene_id = goa.gene_id
WHERE goa.gene_id IS NULL;  -- ★ 关键：右表匹配不上的行，右表列为NULL
-- 等价于：
-- SELECT gene_id, gene_name FROM genes
-- WHERE gene_id NOT IN (SELECT gene_id FROM go_annotations)
-- 但LEFT JOIN + IS NULL效率更高（NOT IN对NULL处理有坑）

-- ========== 4. FULL OUTER JOIN：找在两个数据集中各自独有的基因 ==========
-- 比较两个实验条件的差异基因（找只在一个条件中高表达的基因）
SELECT 
    COALESCE(t.gene_id, c.gene_id) AS gene_id,  -- COALESCE取第一个非NULL值
    t.expression AS treatment_expr,
    c.expression AS control_expr,
    CASE 
        WHEN t.gene_id IS NULL THEN 'Control only'    -- 只在对照组出现
        WHEN c.gene_id IS NULL THEN 'Treatment only'  -- 只在处理组出现
        ELSE 'Both'                                    -- 两组都有
    END AS presence
FROM 
    (SELECT gene_id, expression FROM gene_expression WHERE condition = 'treatment' AND expression > 200) t
FULL OUTER JOIN
    (SELECT gene_id, expression FROM gene_expression WHERE condition = 'control' AND expression > 200) c
ON t.gene_id = c.gene_id;
-- MySQL不支持FULL OUTER JOIN，用 LEFT JOIN UNION RIGHT JOIN 模拟

-- ========== 5. SELF JOIN：在同一张表中比较不同行 ==========
-- 找同一样本中表达量之比 > 2 的基因对（共表达分析）
SELECT 
    e1.gene_id AS gene1,
    e2.gene_id AS gene2,
    e1.expression AS expr1,
    e2.expression AS expr2,
    e1.expression / e2.expression AS ratio
FROM gene_expression e1
JOIN gene_expression e2 
    ON e1.sample_id = e2.sample_id  -- 同一样本
    AND e1.gene_id < e2.gene_id     -- 避免重复对（A-B和B-A只取一个）
WHERE e1.condition = 'treatment'
  AND e2.condition = 'treatment'
  AND e2.expression > 0             -- 防止除零
  AND e1.expression / e2.expression > 2;  -- 表达量比 > 2

-- ========== 6. 多表连接性能优化 ==========
-- 原则：先过滤，再连接（减少连接时的数据量）
-- ❌ 低效：先连接再过滤
SELECT g.gene_name, e.expression
FROM genes g
JOIN gene_expression e ON g.gene_id = e.gene_id
WHERE e.condition = 'treatment';  -- 连接后过滤

-- ✅ 高效：用子查询先过滤再连接（当gene_expression很大时）
SELECT g.gene_name, e.expression
FROM genes g
JOIN (
    SELECT gene_id, expression 
    FROM gene_expression 
    WHERE condition = 'treatment'  -- 先过滤，再连接
) e ON g.gene_id = e.gene_id;

-- ========== 7. 避免笛卡尔积（CROSS JOIN陷阱）==========
-- ❌ 危险：忘记写JOIN条件，产生笛卡尔积
SELECT * FROM genes g, samples s;  -- 无WHERE连接条件！
-- 如果genes有20000行，samples有500行，结果是20000×500=10,000,000行！

-- ✅ 安全：明确写JOIN
SELECT * FROM genes g CROSS JOIN samples s;  -- 明确意图的笛卡尔积

-- ========== 8. 多条件JOIN（复合连接键）==========
-- 某些情况下需要多列才能唯一确定连接关系
SELECT 
    a.gene_id,
    a.expression AS before_expr,
    b.expression AS after_expr
FROM gene_expression a
JOIN gene_expression b 
    ON a.gene_id = a.gene_id             -- 同一基因
    AND a.sample_id = b.sample_id        -- 同一样本
    AND a.timepoint = 0                  -- a = 基线时间点
    AND b.timepoint = 24;               -- b = 24小时时间点

面试常问点¶

Q: LEFT JOIN和WHERE NOT IN有什么区别？推荐哪个？ A: 两者都能找不在另一表的行，但NOT IN对NULL值处理有陷阱（如果子查询返回NULL，整个NOT IN结果为空）。推荐用LEFT JOIN + IS NULL或NOT EXISTS，更安全。
Q: ON和WHERE在JOIN中有什么区别？ A: INNER JOIN中无差别；LEFT/RIGHT JOIN中，ON的过滤在连接前，WHERE在连接后。LEFT JOIN ... ON a.id=b.id AND b.status=1与LEFT JOIN ... ON a.id=b.id WHERE b.status=1结果不同！
Q: 如何优化多表JOIN的性能？ A: 确保JOIN列有索引；小表驱动大表（小表在FROM，大表用JOIN）；先过滤再JOIN；避免在JOIN条件中使用函数。

速查表¶

-- 各类JOIN核心语法
INNER JOIN  ... ON condition    -- 取交集
LEFT JOIN   ... ON condition    -- 保留左表
RIGHT JOIN  ... ON condition    -- 保留右表
FULL OUTER JOIN ... ON condition -- 取并集（MySQL用UNION模拟）
CROSS JOIN                       -- 笛卡尔积（慎用！）

-- 反连接（找不匹配的行）
LEFT JOIN t ON ... WHERE t.id IS NULL  -- 推荐
NOT EXISTS (SELECT 1 FROM t WHERE ...)  -- 也可以