如何用AND/OR/COALESCE/NULLIF实现PostgreSQL动态WHERE过滤？

现有users表，需要基于first_name、last_name、age、state等字段做条件查询，要求用单条SQL语句实现以下逻辑：

• 当传入的参数为NULL时，对应字段的WHERE过滤条件自动失效（等效于该条件写1=1）
• 当参数非空时，才应用该字段的过滤规则

当前使用NodeJS的PostgreSQL客户端传参，现有基础代码示例如下：

execSql({
  text: 'SELECT * FROM users WHERE first_name = $1',
  values: [firstName]
})

需要仅通过AND、OR、COALESCE、NULLIF组合修改SQL语句，实现上述需求。

方法1：使用OR结合参数NULL判断（最直观） #

核心逻辑：对每个字段，构造(参数 IS NULL OR 字段 = 参数)的条件，所有条件用AND连接。当参数为NULL时，参数 IS NULL为真，整个条件自动成立；参数非空时，会执行字段=参数的匹配逻辑。

完整SQL语句：

SELECT *
FROM users
WHERE
  ($1 IS NULL OR first_name = $1)
  AND ($2 IS NULL OR last_name = $2)
  AND ($3 IS NULL OR age = $3)
  AND ($4 IS NULL OR state = $4);

对应的NodeJS调用代码：

execSql({
  text: `SELECT * FROM users 
         WHERE ($1 IS NULL OR first_name = $1)
           AND ($2 IS NULL OR last_name = $2)
           AND ($3 IS NULL OR age = $3)
           AND ($4 IS NULL OR state = $4)`,
  values: [firstName, lastName, age, state]
})

方法2：使用COALESCE简化条件 #

利用COALESCE返回第一个非NULL值的特性，将条件写成字段 = COALESCE(参数, 字段)。当参数为NULL时，COALESCE($1, first_name)返回first_name，等效于first_name = first_name，条件恒成立；参数非空时，直接用参数值匹配字段。

完整SQL语句：

SELECT *
FROM users
WHERE
  first_name = COALESCE($1, first_name)
  AND last_name = COALESCE($2, last_name)
  AND age = COALESCE($3, age)
  AND state = COALESCE($4, state);

对应的NodeJS调用代码：

execSql({
  text: `SELECT * FROM users 
         WHERE first_name = COALESCE($1, first_name)
           AND last_name = COALESCE($2, last_name)
           AND age = COALESCE($3, age)
           AND state = COALESCE($4, state)`,
  values: [firstName, lastName, age, state]
})

方法3：结合NULLIF处理空字符串场景（可选） #

如果需要把传入的空字符串也视为"无过滤"的信号，可以用NULLIF把空字符串转为NULL，再结合方法1的逻辑：

SQL语句示例：

SELECT *
FROM users
WHERE
  ($1 IS NULL OR first_name = NULLIF($1, ''))
  AND ($2 IS NULL OR last_name = NULLIF($2, ''))
  AND ($3 IS NULL OR age = $3)
  AND ($4 IS NULL OR state = NULLIF($4, ''));

对应的NodeJS代码：

execSql({
  text: `SELECT * FROM users 
         WHERE ($1 IS NULL OR first_name = NULLIF($1, ''))
           AND ($2 IS NULL OR last_name = NULLIF($2, ''))
           AND ($3 IS NULL OR age = $3)
           AND ($4 IS NULL OR state = NULLIF($4, ''))`,
  values: [firstName, lastName, age, state]
})

注意事项 #

• 方法1在PostgreSQL中可能有更优的查询计划表现，尤其是当字段存在索引时。
• 如果字段本身允许NULL值，方法2会存在局限性：当字段值为NULL且参数为NULL时，NULL = NULL的结果是UNKNOWN，无法匹配到该记录。这种场景下优先选择方法1。

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