如何在SQL多表全连接时为所有列自动添加表别名前缀？

直接用SELECT *没法自动给列加前缀，得借助数据库的系统表生成带前缀的列列表，再替换到SELECT子句里。下面分主流数据库说明：

MySQL 实现步骤 #

MySQL本身不支持原生FULL JOIN，得用LEFT JOIN + UNION + RIGHT JOIN模拟，先生成带前缀的列字符串：

SELECT GROUP_CONCAT(
  CASE TABLE_NAME 
    WHEN 'TABLE1' THEN CONCAT('T1.', COLUMN_NAME, ' AS T1_', COLUMN_NAME)
    WHEN 'TABLE2' THEN CONCAT('T2.', COLUMN_NAME, ' AS T2_', COLUMN_NAME)
  END SEPARATOR ', '
)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名称' 
  AND TABLE_NAME IN ('TABLE1', 'TABLE2');

执行后会得到类似T1.id AS T1_id, T1.number AS T1_number, T2.name AS T2_name, T2.number AS T2_number的结果，把这段替换原SQL里的SELECT *，再改成模拟全连接的写法：

SELECT T1.id AS T1_id, T1.number AS T1_number, T2.name AS T2_name, T2.number AS T2_number
FROM TABLE1 as T1
LEFT JOIN TABLE2 as T2 ON T1.number=T2.number
UNION
SELECT T1.id AS T1_id, T1.number AS T1_number, T2.name AS T2_name, T2.number AS T2_number
FROM TABLE1 as T1
RIGHT JOIN TABLE2 as T2 ON T1.number=T2.number;

PostgreSQL 实现步骤 #

用系统表生成带前缀的列列表：

SELECT string_agg(
  CASE relname 
    WHEN 'table1' THEN CONCAT('t1.', attname, ' AS t1_', attname)
    WHEN 'table2' THEN CONCAT('t2.', attname, ' AS t2_', attname)
  END, ', '
)
FROM pg_catalog.pg_attribute
JOIN pg_catalog.pg_class ON pg_attribute.attrelid = pg_class.oid
JOIN pg_catalog.pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE nspname = 'public' -- 替换成你的模式名
  AND relname IN ('table1', 'table2')
  AND attnum > 0 AND NOT attisdropped;

把生成的结果替换原SQL的SELECT *，直接执行全连接即可：

SELECT t1.id AS t1_id, t1.number AS t1_number, t2.name AS t2_name, t2.number AS t2_number
FROM table1 as t1
FULL JOIN table2 as t2 ON t1.number=t2.number;

SQL Server 实现步骤 #

通过sys系统表生成列字符串：

SELECT STRING_AGG(
  CASE t.name 
    WHEN 'TABLE1' THEN CONCAT('T1.', c.name, ' AS T1_', c.name)
    WHEN 'TABLE2' THEN CONCAT('T2.', c.name, ' AS T2_', c.name)
  END, ', '
)
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name IN ('TABLE1', 'TABLE2');

将生成的列列表替换SELECT *，执行全连接：

SELECT T1.id AS T1_id, T1.number AS T1_number, T2.name AS T2_name, T2.number AS T2_number
FROM TABLE1 as T1
FULL JOIN TABLE2 as T2 ON T1.number=T2.number;

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