如何用单条SQL语句筛选health_at为NULL或NOT NULL的行？

问题背景 #

有users_ill表结构如下：

id | user_id | health_at (null or timestamp)

需要实现：通过预编译变量$1控制筛选逻辑——变量为NULL时，选出health_at为NULL的行；变量不为NULL时，选出health_at不为NULL的行。

原尝试代码（存在报错问题）：

if(filter_data === 'is_back') {
  let variable = 'NOT NULL'
} else {
  let variable = 'NULL';
}

query = await pg.query(`
  SELECT 
    ui.id, ui.health_at, ui.created_at
  FROM users_ill ui
  INNER JOIN users u
    ON u.id = c.user_id -- 此处存在笔误，应为ui.user_id
  WHERE ui.health_at = $1
  LIMIT 50`,
[variable]
)

报错原因 #

1. SQL里判断NULL不能用=运算符，NULL = NULL的结果是未知，无法匹配行，必须用IS NULL/IS NOT NULL。
2. 传递字符串'NOT NULL'作为参数，会让SQL执行health_at = 'NOT NULL'，这是将时间戳类型和字符串做无效比较，必然报错。

正确修改方案 #

方案一：利用参数的NULL特性构建条件 #

// 筛选health_at非NULL时，给variable设任意非NULL值；反之设为null
let variable = filter_data === 'is_back' ? 1 : null;

query = await pg.query(`
  SELECT 
    ui.id, ui.health_at, ui.created_at
  FROM users_ill ui
  INNER JOIN users u
    ON u.id = ui.user_id
  WHERE 
    ($1 IS NULL AND ui.health_at IS NULL) 
    OR 
    ($1 IS NOT NULL AND ui.health_at IS NOT NULL)
  LIMIT 50`,
[variable]
)

方案二：用布尔值作为判断标记 #

let isBack = filter_data === 'is_back';

query = await pg.query(`
  SELECT 
    ui.id, ui.health_at, ui.created_at
  FROM users_ill ui
  INNER JOIN users u
    ON u.id = ui.user_id
  WHERE 
    CASE WHEN $1 THEN ui.health_at IS NOT NULL ELSE ui.health_at IS NULL END
  LIMIT 50`,
[isBack]
)

方案三：动态构建WHERE子句（无参数传递） #

let whereClause = filter_data === 'is_back' 
  ? 'ui.health_at IS NOT NULL' 
  : 'ui.health_at IS NULL';

query = await pg.query(`
  SELECT 
    ui.id, ui.health_at, ui.created_at
  FROM users_ill ui
  INNER JOIN users u
    ON u.id = ui.user_id
  WHERE ${whereClause}
  LIMIT 50`
)

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