如何用单条SQL查询返回关联表的独立记录/对象？

首先得明确：关系型数据库的SQL本身是基于行集的，没法直接返回你想要的独立对象数组或者嵌套对象结构——这种结构化的结果本质是应用层的需求，不过我们可以通过SQL的扩展功能或者技巧来贴近目标，减少后续处理的工作量。

1. 得到独立记录（两个对象） #

用UNION ALL把两个表的结果合并成两行，需要保证列数和类型一致，示例：

-- 注意：如果method和primitiveType类型不同，需要用CAST统一类型
SELECT 'tableA' AS source, method AS data FROM tableA 
WHERE id = (SELECT tableA_id FROM tableB WHERE id = ?)
UNION ALL
SELECT 'tableB' AS source, primitiveType AS data FROM tableB 
WHERE id = ?;

返回的结果是两行数据：

[
  RowDataPacket { source: "tableA", data: "some value" },
  RowDataPacket { source: "tableB", data: "some value" }
]

你在应用层可以根据source字段把数据转换成对应的独立对象，这种方式只需要一次查询，效率和JOIN差不多。

2. 得到嵌套对象结构 #

利用数据库的JSON函数（比如MySQL的JSON_OBJECT、PostgreSQL的json_build_object），把每个表的字段打包成JSON对象，示例：

SELECT
  JSON_OBJECT('method', tableA.method) AS tableA,
  JSON_OBJECT('primitiveType', tableB.primitiveType) AS tableB
FROM tableB
JOIN tableA ON tableB.tableA_id = tableA.id
WHERE tableB.id = ?;

返回的结果是一行，包含两个JSON字符串字段：

[
  RowDataPacket {
    tableA: "{\"method\": \"some value\"}",
    tableB: "{\"primitiveType\": \"some value\"}"
  }
]

在应用层只需要把这两个JSON字符串解析成对象，就能得到你想要的{tableA: {...}, tableB: {...}}结构，比之前手动拆分前缀字段要更清晰。

额外说明 #

• 如果你用ORM框架（比如Sequelize、TypeORM），它们自带关联查询映射成嵌套对象的功能，完全不需要手动处理SQL；
• 子查询确实会多一次查询开销，上面的两种方式都是单条SQL完成，效率有保障；
• 纯SQL没法直接返回你要的那种“原生嵌套对象”，因为SQL结果集是二维表，这种结构化对象是应用层的抽象。

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