PHP小码农

Appearance

COALESCE / NULLIF / GREATEST / LEAST ​

这四个函数是 SQL 中最常用的「条件值处理」工具：COALESCE 提供首个非 NULL 的兜底值，NULLIF 把「相等」转换为 NULL（经典用途是防除零），GREATEST / LEAST 在一组值中取最大或最小。它们都能嵌入任意表达式位置，是数据清洗、报表兜底与业务规则化简的基础设施。

前置知识

阅读本节前，建议先了解：CASE WHEN 表达式

基础概念 ​

概念术语说明
空值兜底COALESCE返回参数列表中第一个非 NULL 的值，参数不限个数
相等转空值NULLIF两个参数相等时返回 NULL，否则返回第一个参数
取最大值GREATEST返回参数列表中的最大值
取最小值LEAST返回参数列表中的最小值
短路求值Short-Circuit EvaluationCOALESCE 找到首个非 NULL 后不再计算后续参数
防除零Divide-by-Zero Guarda / NULLIF(b, 0) 在 b 为 0 时返回 NULL 而非报错
空值传染NULL PropagationGREATEST / LEAST 中任一参数为 NULL 时结果通常为 NULL
类型归一Type Unification多参数函数要求各参数能归一到同一类型
默认值填充Default Filling用 COALESCE 把 NULL 替换为业务默认值
空串归一Empty-String Normalization用 NULLIF(col, '') 把空字符串转为 NULL

这几个函数与 CASE 的关系：

  • COALESCE(a, b) ⟺ CASE WHEN a IS NOT NULL THEN a ELSE b END
  • NULLIF(a, b) ⟺ CASE WHEN a = b THEN NULL ELSE a END
  • GREATEST / LEAST 无法用简单 CASE 等价表达（涉及多值比较），PostgreSQL 提供了原生实现。
sql
-- 四个函数的最小示例
SELECT
  coalesce(NULL, NULL, '兜底')      AS coalesce_demo,   -- 兜底
  nullif(10, 10)                    AS nullif_demo,     -- NULL
  greatest(3, 9, 5)                 AS greatest_demo,   -- 9
  least(3, 9, 5)                    AS least_demo;      -- 3

语法与用法 ​

函数签名返回
COALESCEcoalesce(v1, v2, ...)第一个非 NULL 的值
NULLIFnullif(v1, v2)v1 = v2 时返回 NULL，否则返回 v1
GREATESTgreatest(v1, v2, ...)最大值
LEASTleast(v1, v2, ...)最小值
sql
-- 最小可运行示例
SELECT
  coalesce(NULL, 0)                     AS a,  -- 0
  nullif('', '')                        AS b,  -- NULL
  greatest(1, NULL)                     AS c,  -- NULL（NULL 传染）
  least('b', 'a', 'c')                  AS d;  -- a（按列排序规则比较）

详细说明 ​

COALESCE：首个非 NULL 的兜底值 ​

COALESCE 接受任意数量的参数，从左到右扫描，返回第一个非 NULL 的值。若全为 NULL，则返回 NULL。参数个数至少为 1。

sql
SELECT
  coalesce(NULL, NULL, 'C')     AS pick_c,     -- C
  coalesce(NULL, 'B', 'C')      AS pick_b,     -- B
  coalesce('A', 'B', 'C')       AS pick_a,     -- A
  coalesce(NULL, NULL)          AS all_null;   -- NULL

典型用途：

sql
-- 1. 字段兜底：优先展示昵称，其次用户名，最后默认值
SELECT coalesce(nickname, username, '匿名用户') AS display_name FROM users;

-- 2. 聚合结果兜底：无数据时返回 0 而非 NULL
SELECT coalesce(sum(amount), 0) AS total_amount FROM orders WHERE status = 'paid';

-- 3. 多列合并：取第一个有值的联系方式
SELECT coalesce(phone, email, wechat, '无联系方式') AS contact FROM users;

短路求值是 COALESCE 的重要特性：一旦找到非 NULL 值，后续参数不会求值。这可用于性能优化，也可用于避免副作用。

sql
-- 若 c1 非 NULL，expensive_fn() 不会被调用
SELECT coalesce(c1, expensive_fn(c2)) FROM t;

与 CASE 相比，COALESCE 更简洁；但需要注意：它不能像 CASE 那样做条件判断（例如「大于 0 才用」），只能判断是否为 NULL。

NULLIF：把相等转为 NULL ​

NULLIF(v1, v2) 在 v1 = v2 时返回 NULL，否则返回 v1。最经典的用法是防除零。

sql
-- 危险！b 为 0 时会报 division by zero
SELECT 100 / 0;  -- 错误！

-- 安全：分母为 0 时返回 NULL
SELECT 100 / NULLIF(0, 0) AS safe_div;  -- NULL

实际查询中：

sql
-- 计算转化率，避免除零
SELECT
  campaign,
  clicks,
  orders,
  round(orders::numeric / NULLIF(clicks, 0), 4) AS conversion_rate
FROM campaigns;

另一个常见用途是把空字符串归一为 NULL，便于统一判空逻辑：

sql
SELECT nullif(trim(description), '') AS description_clean FROM products;

注意 NULLIF 只有两个参数，多于两个需要嵌套：

sql
-- 把多个「无效值」都转成 NULL
SELECT nullif(nullif(status, ''), 'unknown') AS status_clean FROM t;

GREATEST / LEAST：多值取最大最小 ​

两者都接受任意数量参数，参数可以是常量、列或表达式。

sql
SELECT
  greatest(3, 9, 5)          AS max_int,      -- 9
  least(3, 9, 5)             AS min_int,      -- 3
  greatest('2025-01-01'::date, '2025-06-01'::date, '2025-03-01'::date) AS latest_date,
  least(10.5, 2.75, 8.0)     AS min_numeric;  -- 2.75

关键行为：任一参数为 NULL，结果通常为 NULL（NULL 传染）。这一点与很多人直觉中的「忽略 NULL」相反。

sql
-- NULL 传染
SELECT
  greatest(1, NULL, 3) AS with_null,     -- NULL，不是 3
  least(1, NULL, 3)    AS least_null;    -- NULL，不是 1

-- 需要忽略 NULL 时必须先用 COALESCE 兜底
SELECT greatest(coalesce(a, 0), coalesce(b, 0)) FROM t;

在 PostgreSQL 中，GREATEST / LEAST 也支持字符串与日期，比较规则遵循该类型的排序规则。

sql
-- 字符串比较（依赖 collation）
SELECT greatest('apple', 'banana', 'cherry') AS g;  -- cherry

-- 日期比较：常用于取「最后期限」
SELECT greatest(due_date, now()::date) AS effective_due FROM tasks;

三者的组合用法 ​

这几个函数经常组合出现，用于复杂的数据清洗与计算。

sql
-- 带宽限制在 [min_val, max_val] 区间内（clamp）
SELECT
  sku,
  least(greatest(stock, 0), 100) AS clamped_stock   -- 先下限后上限
FROM products;

-- 安全除法 + 兜底默认值
SELECT
  coalesce(round(amount / NULLIF(qty, 0), 2), 0) AS unit_price
FROM orders;

-- 空串、NULL 统一为默认值
SELECT coalesce(nullif(trim(name), ''), '未命名') AS clean_name FROM products;

clamp 的写法顺序很重要：必须先 greatest 抬下限，再 least 压上限；否则先把大值压到上限后再抬下限，虽然本例结果相同，但在上下限可能反转时语义会出错。

与 CASE 的选择 ​

场景推荐写法
只在 NULL 与非 NULL 之间选择COALESCE
判断业务条件（大小、区间、枚举）CASE WHEN
相等即视为无效（如 0、空串）NULLIF
多值取最大最小GREATEST / LEAST
多值取最大最小但需忽略 NULLGREATEST(coalesce(...), ...)

COALESCE 与 CASE 性能基本一致，优化器会做等价改写；GREATEST / LEAST 在多参数时比手写嵌套 CASE 更简洁且更快。

NULLIF 的进阶：唯一约束中的「有效值唯一」 ​

NULLIF 常配合唯一索引使用，实现「空值不参与唯一性检查」的语义。

sql
-- 希望：非空值时唯一，空串与 NULL 视为「无值」不参与去重
CREATE TABLE users (
  id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text
);

CREATE UNIQUE INDEX uq_users_email_valid
  ON users (nullif(lower(trim(email)), ''));

实战示例 ​

步骤 1：建表并造数据 ​

sql
-- 统一的示例库
CREATE DATABASE demo;

DROP TABLE IF EXISTS campaigns;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text    NOT NULL UNIQUE,
  name        text    NOT NULL,
  nickname    text,
  price       numeric(10, 2),
  min_price   numeric(10, 2),
  max_price   numeric(10, 2),
  stock       integer
);

CREATE TABLE users (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username text,
  nickname text,
  phone    text,
  email    text,
  wechat   text
);

CREATE TABLE orders (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   text NOT NULL,
  qty        integer,
  amount     numeric(10, 2)
);

CREATE TABLE campaigns (
  id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text NOT NULL,
  clicks  integer NOT NULL DEFAULT 0,
  orders  integer NOT NULL DEFAULT 0
);

INSERT INTO products (sku, name, nickname, price, min_price, max_price, stock) VALUES
  ('SKU-1001', '27 英寸显示器', '显示器',  1299.00, 999.00, 1599.00, 12),
  ('SKU-1002', '机械键盘',       NULL,      399.00, 299.00, 499.00, -3),
  ('SKU-1003', '无线鼠标',       '鼠标',    159.00, NULL,   NULL,   150),
  ('SKU-1004', 'HDMI 线缆',      NULL,       49.00,  39.00,  59.00, NULL);

INSERT INTO users (username, nickname, phone, email, wechat) VALUES
  ('zhangsan', '张三',   '13800000001', 'zs@example.com', NULL),
  ('lisi',     NULL,     NULL,          'ls@example.com', 'lisi_wx'),
  ('wangwu',   '小王',   '13800000003', NULL,             NULL),
  (NULL,       NULL,     NULL,          NULL,             NULL);

INSERT INTO orders (order_no, qty, amount) VALUES
  ('SO-2001', 2,   2598.00),
  ('SO-2002', 0,    399.00),
  ('SO-2003', NULL, 159.00),
  ('SO-2004', 3,    477.00);

INSERT INTO campaigns (name, clicks, orders) VALUES
  ('春季促销', 1200, 36),
  ('新品首发',    0,  0),
  ('会员日',     800, 12);

步骤 2：COALESCE 多级兜底 ​

sql
SELECT
  id,
  coalesce(nickname, username, '匿名用户') AS display_name,
  coalesce(phone, email, wechat, '无联系方式') AS contact
FROM users
ORDER BY id;
text
 id | display_name |    contact
----+--------------+----------------
  1 | 张三          | 13800000001
  2 | lisi          | ls@example.com
  3 | 小王          | 13800000003
  4 | 匿名用户      | 无联系方式
(4 rows)
sql
-- 聚合兜底：无数据时返回 0
SELECT coalesce(sum(amount), 0) AS total_amount
FROM orders
WHERE qty > 100;   -- 无匹配行
text
 total_amount
--------------
         0.00
(1 row)

步骤 3：NULLIF 防除零 ​

sql
-- 单笔订单均价；qty 为 0 或 NULL 时返回 NULL 而非报错
SELECT
  order_no,
  qty,
  amount,
  round(amount / NULLIF(qty, 0), 2) AS unit_price
FROM orders
ORDER BY order_no;
text
 order_no | qty | amount  | unit_price
----------+-----+---------+------------
 SO-2001  |   2 | 2598.00 |    1299.00
 SO-2002  |   0 |  399.00 |
 SO-2003  |     |  159.00 |
 SO-2004  |   3 |  477.00 |     159.00
(4 rows)
sql
-- 再把 NULL 兜底成 0，用于报表展示
SELECT
  order_no,
  coalesce(round(amount / NULLIF(qty, 0), 2), 0) AS unit_price
FROM orders
ORDER BY order_no;
text
 order_no | unit_price
----------+------------
 SO-2001  |    1299.00
 SO-2002  |       0.00
 SO-2003  |       0.00
 SO-2004  |     159.00
(4 rows)
sql
-- 转化率：避免除零
SELECT
  name,
  clicks,
  orders,
  round(orders::numeric / NULLIF(clicks, 0), 4) AS conversion_rate
FROM campaigns
ORDER BY id;
text
   name   | clicks | orders | conversion_rate
----------+--------+--------+-----------------
 春季促销  |   1200 |     36 |          0.0300
 新品首发  |      0 |      0 |
 会员日    |    800 |     12 |          0.0150
(3 rows)

步骤 4：GREATEST / LEAST 与 NULL 传染 ​

sql
-- 演示 NULL 传染
SELECT
  sku,
  min_price,
  max_price,
  greatest(min_price, max_price) AS raw_greatest,
  least(min_price, max_price)    AS raw_least
FROM products
ORDER BY sku;
text
   sku    | min_price | max_price | raw_greatest | raw_least
----------+-----------+-----------+--------------+-----------
 SKU-1001 |    999.00 |   1599.00 |      1599.00 |    999.00
 SKU-1002 |    299.00 |    499.00 |       499.00 |    299.00
 SKU-1003 |           |           |              |
 SKU-1004 |     39.00 |     59.00 |        59.00 |     39.00
(4 rows)
sql
-- 修正：先用 coalesce 兜底，避免 NULL 传染
SELECT
  sku,
  greatest(coalesce(min_price, 0), coalesce(max_price, 0)) AS greatest_safe,
  least(
    coalesce(min_price, 'Infinity'::numeric),
    coalesce(max_price, 'Infinity'::numeric)
  ) AS least_safe
FROM products
ORDER BY sku;
text
   sku    | greatest_safe | least_safe
----------+---------------+------------
 SKU-1001 |       1599.00 |     999.00
 SKU-1002 |        499.00 |     299.00
 SKU-1003 |          0.00 |  Infinity
 SKU-1004 |         59.00 |      39.00
(4 rows)

步骤 5：clamp 区间限制与空串归一 ​

sql
-- 把库存限制在 [0, 100] 区间
SELECT
  sku,
  stock,
  least(greatest(coalesce(stock, 0), 0), 100) AS clamped
FROM products
ORDER BY sku;
text
   sku    | stock | clamped
----------+-------+---------
 SKU-1001 |    12 |      12
 SKU-1002 |    -3 |       0
 SKU-1003 |   150 |     100
 SKU-1004 |       |       0
(4 rows)
sql
-- 空串与 NULL 统一为默认值
SELECT
  sku,
  coalesce(nullif(trim(nickname), ''), name) AS label
FROM products
ORDER BY sku;
text
   sku    |     label
----------+----------------
 SKU-1001 | 显示器
 SKU-1002 | 机械键盘
 SKU-1003 | 鼠标
 SKU-1004 | HDMI 线缆
(4 rows)

注意事项 ​

  • GREATEST / LEAST 遇 NULL 返回 NULL，不会被忽略。 这是与「取最大最小」直觉最大的偏差，务必先用 COALESCE 兜底。
sql
-- 错误预期：希望得到 3
SELECT greatest(1, NULL, 3);  -- NULL
-- 正确：先兜底
SELECT greatest(coalesce(1, 0), coalesce(NULL, 0), coalesce(3, 0));  -- 3
  • COALESCE 不能替代业务条件判断。 它只判断是否为 NULL，判断范围或枚举仍需 CASE。
sql
-- 错误理解：以为会返回 0（但 0 不是 NULL，会原样返回）
SELECT coalesce(0, 100);  -- 0
  • NULLIF(a, b) 只比较 a 与 b，不处理多值。 要排除多个无效值需嵌套调用。
sql
-- 排除 '' 与 'unknown'
SELECT nullif(nullif(status, ''), 'unknown') FROM t;
  • 除零错误只被 NULLIF 屏蔽，不会被 COALESCE 屏蔽。 coalesce(b, 0) 后仍可能为 0。
sql
-- 错误！b 为 0 时依然报错
SELECT a / coalesce(b, 0);
-- 正确
SELECT a / NULLIF(b, 0);
  • 函数参数类型必须能归一到同一类型。 混用文本与数字需要显式转换，否则报错。
sql
SELECT coalesce(NULL, 1) || 'x';        -- 可以（隐式转 text）
SELECT greatest(1, '2');                -- 错误！类型不兼容
SELECT greatest(1, '2'::int);           -- 正确

  • 字符串的 GREATEST / LEAST 依赖排序规则（collation）。 不同 collation 下大小写与重音的处理不同，跨库迁移时结果可能变化。

  • coalesce 在 WHERE 中包裹索引列会让索引失效。

sql
-- 不推荐：索引失效
WHERE coalesce(price, 0) > 100
-- 推荐：拆成等价条件
WHERE (price > 100) OR (price IS NULL AND 0 > 100)
  • NULLIF 判断基于等值比较，NULL 与任何值比较都不相等，因此 nullif(NULL, 'x') 返回 NULL（原样返回第一个参数）。

最佳实践 ​

  1. 聚合结果一律用 COALESCE 兜底为 0，避免前端拿到 NULL 需要额外判空。
sql
SELECT coalesce(sum(amount), 0) AS total FROM orders;
  1. 任何除法都用 NULLIF(分母, 0) 包裹，这是防止线上 division by zero 报错的最低成本手段。
sql
SELECT amount / NULLIF(qty, 0) AS unit_price FROM order_items;
  1. GREATEST / LEAST 的每个参数都先用 COALESCE 兜底，把 NULL 传染风险消灭在函数调用内部。
sql
SELECT greatest(coalesce(a, 0), coalesce(b, 0), coalesce(c, 0)) FROM t;
  1. 用 NULLIF + 唯一索引实现「有效值唯一」，让空串与 NULL 不参与去重。
sql
CREATE UNIQUE INDEX uq_users_email ON users (nullif(lower(trim(email)), ''));
  1. 多级兜底写成一个 COALESCE 而不是嵌套 CASE，可读性与性能都更好。
sql
SELECT coalesce(nickname, username, email, '匿名') FROM users;

  1. clamp 时先抬下限再压上限：least(greatest(v, lo), hi)，顺序固定便于统一规范。

  2. 把兜底默认值定义成常量或函数，避免在多个查询里硬编码字符串，便于统一修改。

sql
CREATE OR REPLACE FUNCTION display_name(nick text, uname text)
RETURNS text LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce(nullif(trim(nick), ''), nullif(trim(uname), ''), '匿名用户');
$$;
  1. 避免在 WHERE 与 JOIN 条件中对索引列使用这些函数。若必需，考虑建立对应的表达式索引。
sql
-- 若查询固定为 mode，建立表达式索引
CREATE INDEX idx_users_email_norm ON users (nullif(lower(trim(email)), ''));

下一节

继续学习：psql 元命令（\d / \dt / \du）

参考链接 ​