PostgreSQL 19重磅升级：VALUES子句直接JOIN，告别IN列表与UNION ALL的繁琐

1. 从“能用”到“好用”一个被忽视的语法缺口如果你在项目里用过 PostgreSQL大概率遇到过这样的场景需要根据一个列表里的值去另一张表里批量查询对应的记录。比如你手头有一串用户 ID想查出这些用户的所有订单。在 PostgreSQL 18 及之前的版本里最直接或者说最“教科书”的写法是使用IN子句或者用UNION ALL把多个查询拼起来再高级点可能会用LATERAL JOIN或者unnest()函数把数组展开。这些方法都能完成任务没错它们“能用”。但作为一个常年泡在数据库里的开发者或 DBA你肯定能感觉到一丝别扭。IN子句在面对超长列表时性能可能是个未知数查询计划器不一定总能给出最优解。用UNION ALL写起来又臭又长毫无美感可言。至于LATERAL JOIN它功能强大但语法稍显复杂对于简单的值列表查询来说有点“杀鸡用牛刀”的感觉。我们内心深处一直期待一个更简洁、更直观、更符合人类思维习惯的语法就像其他一些现代数据库已经提供的那样——一个能让我们直接把一组值当作一张“虚拟表”来JOIN的语法。这个期待在 PostgreSQL 19 中终于变成了现实。它引入了一个全新的、极具实用价值的语法VALUES子句现在可以直接在FROM子句中作为表源使用并且支持更强大的JOIN和WITH ORDINALITY功能。这听起来可能只是一个语法糖但对于日常开发效率、代码可读性以及某些场景下的查询性能来说这无疑是一次“重磅”升级。它补齐了 PostgreSQL 在临时数据集处理方面长期存在的一个小缺口让“好用”成为了新的标准。2. 新旧对比为什么我们一直需要它在深入新语法之前我们先回顾一下在没有它的时候我们是如何处理“用一组已知值去查询”这个高频需求的。通过对比你才能深刻体会到新语法带来的简洁与优雅。2.1 传统方法一IN 列表这是最入门级的方法。假设我们有一个商品表products现在想查询 ID 为 101, 205, 333 这三个商品的信息。SELECT * FROM products WHERE id IN (101, 205, 333);优点极其简单直观对于少量固定值它是首选。缺点与隐患动态构建困难当这个值列表来自应用程序的变量比如一个数组时构建这个 SQL 字符串需要小心处理引号和注入问题。虽然预处理语句可以解决安全问题但拼接(?, ?, ?)占位符本身也略显繁琐。性能不确定性列表很长时比如上千个值查询优化器可能无法生成最优计划。它可能选择对products表进行全表扫描并逐一匹配IN列表而不是使用索引。虽然 PostgreSQL 的优化器在这方面已经相当智能但在极端情况下或复杂查询中这仍是一个潜在风险点。无法关联其他列IN只能匹配单个字段。如果你的查询条件是基于多个字段的组合例如同时匹配user_id和order_dateIN就无能为力了。2.2 传统方法二使用UNION ALL创建临时行集为了解决IN无法处理多列的问题或者为了获得更可控的执行计划我们有时会这样做SELECT p.* FROM products p JOIN ( SELECT 101 AS id, 2023-01-01::date AS effective_date UNION ALL SELECT 205, 2023-02-01::date UNION ALL SELECT 333, 2023-03-01::date ) AS input_data ON p.id input_data.id AND p.release_date input_data.effective_date;优点可以模拟一个具有多列的临时表并且查询计划相对清晰。缺点语法极其冗长和重复。每增加一行数据就要重复一遍SELECT ... UNION ALL。代码维护是一场噩梦尤其是在值列表动态生成时拼接 SQL 的复杂度陡增。2.3 传统方法三使用unnest()与数组这是 PostgreSQL 特有的、比较优雅的一种方式特别适合处理来自应用程序的数组。SELECT p.* FROM products p JOIN unnest(ARRAY[101, 205, 333]) WITH ORDINALITY AS input(id, rn) ON p.id input.id; -- 或者用于多列需要组合数组 SELECT p.* FROM products p JOIN ( SELECT * FROM unnest(ARRAY[101, 205, 333], ARRAY[2023-01-01::date, 2023-02-01::date, 2023-03-01::date]) AS t(id, effective_date) ) AS input_data ON p.id input_data.id AND p.release_date input_data.effective_date;优点与程序语言结合好能利用数组类型WITH ORDINALITY可以保留元素顺序返回行号。缺点语法仍然不够直观。unnest多个数组来构造多列时需要保持数组长度一致理解起来有额外的心智负担。对于简单的常量值列表写起来也不够直接。所有这些方法都绕了一个弯子我们本质上就是想声明一个小型、临时的数据集然后把它当作一张表来用。为什么不能有一个更直接的语法呢PostgreSQL 19 的VALUES子句增强正是对这个问题的直接回应。3. PostgreSQL 19 新语法精讲VALUES作为表表达式PostgreSQL 中的VALUES命令本身并不新它一直用于生成常量表通常用在INSERT语句中或者单独执行以产生一组行。但在之前的版本中它不能在FROM子句中自由地与其他表进行JOIN。PostgreSQL 19 消除了这个限制。3.1 基础用法直接作为数据源现在你可以像使用子查询或表名一样在FROM子句中使用VALUES。SELECT * FROM (VALUES (1, Alice), (2, Bob), (3, Charlie)) AS t(id, name);这将直接返回一个三行的结果集。这本身已经很有用比如快速生成测试数据或配置项。但它的威力在于可以无缝地参与到更复杂的查询中。3.2 核心增强与表进行 JOIN这才是“补齐缺口”的关键。我们可以直接用VALUES创建临时数据集然后JOIN目标表。场景复现查询特定商品ID的信息。SELECT p.* FROM products p INNER JOIN (VALUES (101), (205), (333)) AS input(id) ON p.id input.id;这条查询清晰表达了意图“从 products 表里找出那些 ID 在下面这个列表里的记录”。逻辑层次分明比IN子句更具关系代数美感也更容易让优化器理解。多列 JOIN 场景查询特定商品在特定生效日期之后的信息。SELECT p.* FROM products p INNER JOIN ( VALUES (101, 2023-01-01::date), (205, 2023-02-01::date), (333, 2023-03-01::date) ) AS input(id, effective_date) ON p.id input.id AND p.release_date input.effective_date;现在多条件关联变得如此简单和直观。你定义了一个临时表input它有两列然后像对待普通表一样进行等值和非等值JOIN。代码的可读性和可维护性相比UNION ALL版本有了质的飞跃。3.3 进阶利器WITH ORDINALITYWITH ORDINALITY是 PostgreSQL 中一个非常实用的语法它可以为集合返回函数如unnest()、generate_series()的结果添加一个行号。现在这个功能也支持VALUES子句了。SELECT ordinality, id, name FROM (VALUES (100, Apple), (200, Banana), (300, Cherry)) WITH ORDINALITY AS t(id, name, ordinality);或者更常见的写法将WITH ORDINALITY放在别名之后SELECT t.* FROM (VALUES (100, Apple), (200, Banana), (300, Cherry)) AS t(id, name) WITH ORDINALITY;执行结果会多出一列ordinality值分别为 1, 2, 3。这有什么用场景一保持输入顺序。当你需要按照VALUES列表的顺序处理结果时这个行号就是天然的排序依据。例如你需要按照给定的 ID 顺序返回商品列表并在前端保持同样的顺序展示。SELECT t.ordinality, p.* FROM (VALUES (333), (101), (205)) WITH ORDINALITY AS input(id, ordinality) JOIN products p ON p.id input.id ORDER BY input.ordinality;这样返回的商品顺序就会是 ID 333、101、205与你输入的列表顺序完全一致。这在处理批量操作并需要按序报告结果时非常关键。场景二生成序列号或进行行级计算。在复杂的报表或数据转换中你可能需要为这批特定数据添加一个从1开始的序列号。SELECT ROW_NUMBER() OVER (ORDER BY input.ordinality) as final_seq, input.ordinality as input_seq, p.name, p.price FROM (VALUES (101), (205), (333)) WITH ORDINALITY AS input(id, ordinality) JOIN products p ON p.id input.id;3.4 性能初探与优化器提示从语义上看FROM (VALUES ...) JOIN ...和IN (...)是等价的。那么性能有区别吗在大多数情况下PostgreSQL 的查询优化器足够聪明能为这两种写法生成完全相同的执行计划。你可以使用EXPLAIN ANALYZE来验证。但是新语法在某些场景下可能为优化器提供了更清晰的信息。特别是当VALUES列表非常长或者与多个表进行复杂关联时将其显式定义为一个“表”可能有助于优化器更好地估算行数Cardinality Estimation从而选择更优的连接顺序和连接算法如 Hash Join、Merge Join。注意虽然新语法很强大但它并非在所有情况下都是性能银弹。对于极其简单的单字段等值过滤IN列表可能仍然是编译和解析速度最快的。但对于复杂的、多条件的、或需要保持顺序的关联查询VALUES子句在可读性和可维护性上带来的收益远远超过其微乎其微的解析开销。我的建议是将清晰度和正确性放在首位在遇到实际性能瓶颈时再针对性地使用EXPLAIN工具进行分析。4. 实战应用场景深度剖析语法是骨架场景才是血肉。下面我们通过几个真实开发中常见的场景来看看这个新语法如何大显身手。4.1 场景一批量数据校验与更新这是后端服务中最常见的场景之一。你收到一个批量请求包含多个需要处理的项目如订单ID、用户ID。你需要先校验这些ID是否有效存在于数据库中然后对有效的ID执行更新操作。旧方式繁琐且易错-- 可能需要执行多次查询或者在应用层拆分逻辑新语法方式清晰且原子WITH input_data AS ( SELECT * FROM (VALUES (1001, paid), (1002, shipped), (1005, cancelled) -- 假设1005在数据库中不存在 ) AS t(order_id, new_status) ), valid_orders AS ( SELECT o.id, i.new_status FROM orders o INNER JOIN input_data i ON o.id i.order_id -- 这里可以加入其他校验如状态机约束 o.status IN (pending, confirmed) ) UPDATE orders o SET status vo.new_status, updated_at NOW() FROM valid_orders vo WHERE o.id vo.id RETURNING o.id, o.status; -- 只返回成功更新的订单这个WITH查询CTE将输入数据定义为input_data然后通过JOIN过滤出数据库中存在的有效订单 (valid_orders)最后基于这个有效集合进行更新。逻辑链条清晰在一个语句中完成了校验和更新保证了原子性。RETURNING子句可以明确告诉调用方哪些ID被成功处理了。4.2 场景二动态配置查询与报表生成假设你有一个报表需要根据每天手动指定的几个关键指标如特定商品品类、特定地区来生成数据。这些指标配置是动态的可能存储在配置表也可能由前端传递。-- 假设我们从某个配置源获得了这些“关键商品”列表 SELECT c.category_name, input.product_sku, SUM(s.amount) as total_sales, COUNT(DISTINCT s.customer_id) as unique_customers FROM sales s JOIN products p ON s.product_id p.id JOIN categories c ON p.category_id c.id -- 这里是核心使用VALUES定义本次报表关心的商品SKU INNER JOIN (VALUES (SKU-ALPHA-001), (SKU-BETA-200), (SKU-GAMMA-500) ) AS input(product_sku) ON p.sku input.product_sku WHERE s.sale_date BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY c.category_name, input.product_sku ORDER BY total_sales DESC;通过将关注点列表内联在 SQL 中报表查询的逻辑变得自包含且易于理解。如果需要修改关注商品只需改动VALUES列表即可无需修改复杂的JOIN或WHERE条件结构。4.3 场景三单元测试与数据夹具Fixtures构建为数据库相关的代码编写单元测试时经常需要准备特定的测试数据。新语法让在测试用例中内联定义期望数据和实际数据的对比变得非常方便。-- 在一个测试中验证某个函数或视图返回的结果 -- 1. 准备测试输入使用VALUES插入临时数据 CREATE TEMP TABLE test_input AS SELECT * FROM (VALUES (1, Test User 1, active), (2, Test User 2, inactive) ) AS data(user_id, name, status); -- 2. 调用被测试的函数/视图获取实际结果 CREATE TEMP TABLE actual_result AS SELECT * FROM my_complex_function((SELECT array_agg(user_id) FROM test_input)); -- 3. 定义期望结果 CREATE TEMP TABLE expected_result AS SELECT * FROM (VALUES (1, Processed: Test User 1, 100), (2, Skipped: Test User 2, 0) ) AS exp(id, message, score); -- 4. 比较实际与期望使用FULL OUTER JOIN找出差异 SELECT COALESCE(a.id, e.id) as id, a.message as actual_message, e.message as expected_message, a.score as actual_score, e.score as expected_score, CASE WHEN a.message IS DISTINCT FROM e.message OR a.score ! e.score THEN MISMATCH ELSE OK END as status FROM actual_result a FULL OUTER JOIN expected_result e ON a.id e.id WHERE a.message IS DISTINCT FROM e.message OR a.score ! e.score OR a.id IS NULL OR e.id IS NULL;这种方式使得测试逻辑和数据高度集中在一个脚本或测试用例中易于阅读和维护。VALUES子句充当了数据夹具的角色。4.4 场景四解决“反直觉”的 NOT IN 与 NULL 问题这是一个经典的 SQL 陷阱。当你使用NOT IN (subquery)时如果子查询返回的结果集中包含NULL值那么整个NOT IN条件的结果将永远是NULL即FALSE导致查询结果为空。这常常让开发者困惑。有问题的旧写法-- 假设 subquery 可能返回 NULL SELECT * FROM table_a WHERE id NOT IN (SELECT id FROM table_b WHERE ...);如果(SELECT id FROM table_b ...)结果中有任意一个NULL那么table_a中的所有行都不会被返回。更安全的新语法写法SELECT a.* FROM table_a a LEFT JOIN (VALUES (1), (2), (NULL), (3)) AS exclude_list(id) ON a.id exclude_list.id WHERE exclude_list.id IS NULL;通过LEFT JOIN ... WHERE ... IS NULL的模式可以安全地实现“不在列表中”的逻辑即使列表里有NULL值也不会影响最终结果。虽然这个模式本身不新但结合VALUES子句我们可以更清晰、更安全地构造这个“排除列表”。5. 迁移建议、兼容性与周边工具生态5.1 版本兼容性与渐进式迁移PostgreSQL 19 目前尚未正式发布截至我撰写本文时。此功能将在 PostgreSQL 19 及以后版本中可用。对于正在运行旧版本18 及以下的系统你暂时无法使用此语法。迁移建议评估与规划首先在开发或测试环境的 PostgreSQL 19 实例上用你的业务查询进行测试。对比新旧写法的执行计划确认功能正确性和性能表现。渐进式重构不要试图一次性重写所有旧查询。优先在以下场景中应用新语法新的开发任务。正在修改的、涉及复杂IN列表或UNION ALL的旧查询。可读性差、难以维护的查询。保持向后兼容如果你的应用需要同时支持新旧版本的 PostgreSQL可以考虑在应用层进行抽象。例如编写一个辅助函数或使用查询构建器根据数据库版本动态生成合适的 SQLIN列表或VALUES ... JOIN。不过这增加了复杂度需要权衡收益。5.2 与 ORM 和查询构建器的配合流行的 ORM如 SQLAlchemy、Django ORM、Hibernate和查询构建器需要时间适配新语法。在早期你可能需要直接使用原生 SQL 片段来享受这个特性。以 Python SQLAlchemy 为例你可以这样使用from sqlalchemy import text, bindparam # 假设 ids 是一个 ID 列表 ids [101, 205, 333] # 构建 VALUES 子句的 SQL 文本和参数 values_clause VALUES , .join([f(:id_{i}) for i in range(len(ids))]) params {fid_{i}: id_ for i, id_ in enumerate(ids)} query text(f SELECT p.* FROM products p INNER JOIN ({values_clause}) AS input(id) ON p.id input.id ).bindparams(**params)期待未来 ORM 能提供更优雅的 API 来支持此功能例如session.query(Product).join(values([...]), ...)。5.3 在存储过程和函数中的运用新语法在 PL/pgSQL 函数中同样威力巨大可以简化很多逻辑。CREATE OR REPLACE FUNCTION get_products_by_ids(id_list integer[]) RETURNS SETOF products AS $$ BEGIN RETURN QUERY SELECT p.* FROM products p JOIN (SELECT unnest(id_list) as id) AS input ON p.id input.id; -- 或者如果 id_list 是常量直接使用 VALUES 更清晰 -- JOIN (VALUES (101), (205), (333)) AS input(id) ON p.id input.id; END; $$ LANGUAGE plpgsql STABLE;对于需要复杂输入参数的函数VALUES可以帮你构造一个清晰的结构化临时表作为中间处理步骤。5.4 性能考量与最佳实践列表长度对于非常短的列表 10个值IN和VALUES ... JOIN的性能差异可以忽略不计选择可读性更高的。对于长列表数百上千VALUES ... JOIN可能更有利于优化器但务必用EXPLAIN ANALYZE验证。极端情况下如果列表过长考虑使用临时表。索引利用确保JOIN条件上的字段如p.id有索引。新语法不改变这个根本原则。查询计划缓存对于使用预处理语句prepared statements的应用带有动态VALUES列表的查询可能无法像简单IN查询那样被完美地缓存和复用计划。但这通常不是问题因为VALUES列表的变化会被视为查询文本的一部分变化。在性能敏感的场景下可以稍作测试。首选 CTE 提高可读性对于复杂的查询将VALUES子句放在 CTE (WITH子句) 中可以极大地提升整个查询语句的结构清晰度。WITH important_users AS ( SELECT * FROM (VALUES (1001, admin), (1002, vip), (1003, moderator) ) AS t(user_id, role_tag) ) SELECT u.username, i.role_tag, COUNT(o.id) as order_count FROM users u JOIN important_users i ON u.id i.user_id LEFT JOIN orders o ON u.id o.user_id GROUP BY u.username, i.role_tag;PostgreSQL 19 将VALUES子句提升为一等公民允许其在FROM子句中自由JOIN这虽然只是语法层面的演进却实实在在地触及了开发者日常工作的痛点。它用更符合关系模型思维的方式解决了临时数据集处理的表达难题。从IN列表的模糊到UNION ALL的冗长再到unnest数组的特定性我们终于有了一个通用、清晰且强大的标准答案。当你在 PostgreSQL 19 中写下FROM ... JOIN (VALUES ...) ON ...时你写的不仅是 SQL更是一种清晰表达意图的代码哲学。这个“重磅新语法”补上的不仅是一个功能缺口更是许多开发者心中对 PostgreSQL 简洁之美的一份期待。