SQL联表查询如何用关联表字段值替换外键并保持原表结构

问题原因 #

你用SELECT *联表查询时，数据库会返回所有参与连接的表的全部列，因此结果里会包含三张表各自的id字段、两张关联表的name字段，自然会出现字段冗余、列顺序/值不符合预期的问题。

标准SQL没有提供「选所有列但排除指定列」的原生语法，要实现你要的「返回结构和table_1完全一致，外键列替换为关联表name值」的需求，可以根据你用的数据库选下面的方案：

方案1：通用标准SQL写法（全数据库兼容） #

直接明确指定返回的字段，将外键位替换为关联表的name值，别名和原外键列名保持一致即可，写法最稳妥无歧义：

SELECT 
  t1.id,
  t2.name AS fk_table_2,
  t3.name AS fk_table_3
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.fk_table_2 = t2.id
INNER JOIN table_3 t3 ON t1.fk_table_3 = t3.id;

这个写法返回的列顺序、列名和table_1完全一致，没有冗余字段，不会出现列错位问题。

方案2：* EXCEPT简化写法（支持该语法的数据库可用） #

如果你用的是PostgreSQL、BigQuery、Snowflake等支持* EXCEPT语法的数据库，可以不用枚举table_1的非外键字段，写法更简洁：

SELECT 
  t1.* EXCEPT (fk_table_2, fk_table_3),
  t2.name AS fk_table_2,
  t3.name AS fk_table_3
FROM table_1 t1
INNER JOIN table_2 t2 ON t1.fk_table_2 = t2.id
INNER JOIN table_3 t3 ON t1.fk_table_3 = t3.id;

逻辑是先取出table_1除了两个存ID的外键列之外的所有字段，再把关联查到的name值用原外键列名补入，最终结构和原表完全一致。

方案3：元数据自动生成字段列表（不支持* EXCEPT的数据库偷懒方案） #

如果你用MySQL这类不支持列排除语法的数据库，又不想每次手动枚举字段，可以通过查询系统表自动生成SELECT子句，以MySQL为例：

-- 替换下面语句里的'你的数据库名'为实际使用的库名
SELECT GROUP_CONCAT(
    CASE 
        WHEN COLUMN_NAME = 'fk_table_2' THEN 't2.name AS fk_table_2'
        WHEN COLUMN_NAME = 'fk_table_3' THEN 't3.name AS fk_table_3'
        ELSE CONCAT('t1.', COLUMN_NAME)
    END ORDER BY ORDINAL_POSITION SEPARATOR ',\n  '
) AS select_clause
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名' 
  AND TABLE_NAME = 'table_1';

执行该语句会直接输出拼好的字段列表，复制到SELECT后搭配联表语句即可使用，后续table_1字段变动时重新执行生成即可，不用手动维护字段列表。

注意：不要用SELECT t1.*, t2.name AS fk_table_2, t3.name AS fk_table_3这类写法，这种写法会同时返回原存ID的外键列和别名的name列，造成重名冲突，大部分数据库驱动取数时会出现值覆盖、取数错误的问题。

内容的提问来源于stack exchange，提问作者Neotenic Primate