大家好，我是讯享网，很高兴认识大家。

SQL 条件表达式（CASE）可以基于不同条件产生不同的结果，实现类似于编程语言中的 IF-THEN-ELSE 逻辑处理功能。例如，根据员工的 KPI 计算相应的涨薪幅度，根据学生考试成绩评出优秀、良好、及格等。

本文比较五种主流数据库条件表达式的实现和差异，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

条件表达式	MySQL	Oracle	SQL Server	PostgreSQL	SQLite
简单 CASE 表达式	✔️	✔️	✔️	✔️	✔️
搜索 CASE 表达式	✔️	✔️	✔️	✔️	✔️
nullif 函数	✔️	✔️	✔️	✔️	✔️
coalesce 函数	✔️	✔️	✔️	✔️	✔️
专有函数	if、ifnull	decode、nvl、nvl2	isnull、iif		ifnull、iif

简单 CASE 表达式

简单 CASE 表达式的语法如下：

CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ... [ELSE default] END 

讯享网

语句执行时首先计算 expression 的值，然后将该值和第 1 个 WHEN 子句中的数据（value1）进行比较。如果二者相等，则返回对应 THEN 子句中的结果（result1）；如果二者不相等，则继续将该值和第 2 个 WHEN 子句中的数据（value2）进行比较，如果二者相等，则返回对应 THEN子句中的结果（result2）；依此类推。如果没有找到相等的数据，返回 ELSE 子句中的默认结果（default）；如果没有指定 ELSE 子句，返回空值。

简单 CASE 达式的计算过程如下图所示。

讯享网

例如，以下查询使用简单 CASE 表达式将员工的部门编号转换为相应的名称：

讯享网SELECT emp_name, CASE dept_id WHEN 1 THEN '行政管理部' WHEN 2 THEN '人力资源部' WHEN 3 THEN '财务部' WHEN 4 THEN '研发部' WHEN 5 THEN '销售部' WHEN 6 THEN '保卫部' ELSE '其他部门' END AS "部门名称" FROM employee; 

查询返回的结果如下：

员工姓名|部门编号|部门名称 ------|-------|-------- 刘备 | 1|行政管理部 关羽 | 2|行政管理部 张飞 | 3|行政管理部 诸葛亮 | 4|人力资源部 黄忠 | 5|人力资源部 魏延 | 6|人力资源部 孙尚香 | 7|财务部 孙丫鬟 | 8|财务部 ... 

简单 CASE 表达式在进行条件判断时使用的是等值比较（=），只能处理简单的比较逻辑。如果想要实现复杂的逻辑处理，例如根据学生考试成绩范围评出优秀、良好等，就需要使用更加强大的搜索 CASE 表达式。

搜索 CASE 表达式

搜索 CASE 表达式的语法如下：

讯享网CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... [ELSE default] END 

语句执行时首先判断第 1 个 WHEN 子句中的条件（condition1）是否成立。如果成立，则返回对应 THEN 子句中的结果（result1）；如果不成立，则继续判断第 2 个 WHEN 子句中的条件（condition2）是否成立，如果成立则返回对应 THEN 子句中的结果（result2）；依此类推。如果没有任何条件成立，返回 ELSE 子句中的默认结果（default）；如果没有指定 ELSE 子句，返回空值。

搜索 CASE 达式的计算过程如下图所示。

前文中的简单 CASE 表达式示例可以使用等价的搜索 CASE 表达式实现：

SELECT emp_name AS "员工姓名",emp_id AS "部门编号", CASE WHEN dept_id = 1 THEN '行政管理部' WHEN dept_id = 2 THEN '人力资源部' WHEN dept_id = 3 THEN '财务部' WHEN dept_id = 4 THEN '研发部' WHEN dept_id = 5 THEN '销售部' WHEN dept_id = 6 THEN '保卫部' ELSE '其他部门' END AS "部门名称" FROM employee; 

搜索 CASE 表达式中的判断条件可以像 WHERE 子句中的过滤条件一样复杂。例如，以下查询基于员工的月薪将他们的收入分为“高”、“中”、“低”三个级别：

讯享网SELECT emp_name AS "员工姓名",salary AS "月薪", CASE WHEN salary < 10000 THEN '低收入' WHEN salary < 20000 THEN '中收入' ELSE '高收入' END AS "收入级别" FROM employee; 

月薪低于 10 000 元时，返回“低收入”；否则，如果月薪低于 20 000 元（大于或等于 10 000元），返回“中收入”；月薪高于或等于 20 000 元，返回“高收入”。查询返回的结果如下：

员工姓名|月薪 |收入级别 ------|--------|------- 刘备 |30000.00|高收入 关羽 |26000.00|高收入 张飞 |24000.00|高收入 诸葛亮 |24000.00|高收入 黄忠 | 8000.00|低收入 魏延 | 7500.00|低收入 孙尚香 |12000.00|中收入 孙丫鬟 | 6000.00|低收入 ... 

CASE 表达式也可以在其他子句中使用，包括 WHERE、ORDER BY 等子句。例如，以下语句使用 CASE 表达式实现了空值的自定义排序：

讯享网SELECT emp_name AS "员工姓名", CASE WHEN bonus IS NULL THEN 0 ELSE bonus END AS "奖金" FROM employee WHERE dept_id = 2 ORDER BY CASE WHEN bonus IS NULL THEN 0 ELSE bonus END; 

其中，ORDER BY 子句中的 CASE 表达式将 bonus 为空的数据转换为 0，从而实现了空值排在最前的效果。查询返回的结果如下：

员工姓名|奖金 ------|------- 黄忠 | 0 魏延 | 0 诸葛亮 |8000.00 

CASE 表达式是 SQL 中的一个非常实用的功能，而且在 5 种数据库中的实现一致。

案例分析

假如公司即将成立 20 周年，打算给全体员工发放一个周年庆礼品。发放礼品的规则如下：

• 截至 2020 年入职年限不满 10 年的员工，男性员工的礼品为手表一块，女性员工的礼品为化妆品一套；
• 截至 2020 年入职年限满 10 年且不满 15 年的员工，男性员工的礼品为手机一部，女性员工的礼品为项链一条；
• 截至 2020 年入职年限满 15 年的员工，不论男女，礼品统一为电脑一台。

现在，人事部门需要知道为每位员工发放什么礼品。如何通过查询语句得到这些信息呢？搜索 CASE 表达式非常合适这类逻辑条件的处理，我们可以使用以下语句：

讯享网-- Oracle、MySQL 以及 PostgreSQL SELECT emp_name AS "员工姓名", hire_date AS "入职日期", CASE WHEN EXTRACT(YEAR FROM hire_date)> 2011 AND sex = '男' THEN '手表' WHEN EXTRACT(YEAR FROM hire_date) > 2011 AND sex = '女' THEN '化妆品' WHEN EXTRACT(YEAR FROM hire_date) > 2006 AND sex = '男' THEN '手机' WHEN EXTRACT(YEAR FROM hire_date) > 2006 AND sex = '女' THEN '项链' ELSE '电脑' END AS "礼品" FROM employee; 

除搜索 CASE 表达式外，我们还使用了 EXTRACT 函数提取员工的入职年份。以上查询适用于 Oracle、MySQL 以及 PostgreSQL。查询返回的结果如下：

员工姓名|入职日期 |礼品 ------|----------|--- 刘备 |2000-01-01|电脑 关羽 |2000-01-01|电脑 张飞 |2000-01-01|电脑 ... 廖化 |2009-02-17|手机 关平 |2011-07-24|手机 赵氏 |2011-11-10|项链 ... 

Microsoft SQL Server 可以使用 DATEPART 函数提取日期中的信息，例如：

讯享网-- Microsoft SQL Server SELECT emp_name AS "员工姓名", hire_date AS "入职日期", CASE WHEN DATEPART(YEAR, hire_date) > 2011 AND sex = '男' THEN '手表' WHEN DATEPART(YEAR, hire_date) > 2011 AND sex = '女' THEN '化妆品' WHEN DATEPART(YEAR, hire_date) > 2006 AND sex = '男' THEN '手机' WHEN DATEPART(YEAR, hire_date) > 2006 AND sex = '女' THEN '项链' ELSE '电脑' END AS "礼品" FROM employee; 

SQLite 可以使用 STRFTIME 函数提取日期中的信息，例如：

-- SQLite SELECT emp_name AS "员工姓名", hire_date AS "入职日期", CASE WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2011 AND sex = '男' THEN '手表' WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2011 AND sex = '女' THEN '化妆品' WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2006 AND sex = '男' THEN '手机' WHEN CAST(STRFTIME('%Y', hire_date) AS INT) > 2006 AND sex = '女' THEN '项链' ELSE '电脑' END AS "礼品" FROM employee; 

STRFTIME 函数返回的数据类型为字符串，我们通过 CAST 函数将其转换为整数。

NULLIF 函数

NULLIF(expr1, expr2)函数接受两个输入参数，如果第 1 个参数等于第 2 个参数，返回空值；否则，返回第 1 个参数的值。例如，以下查询演示了 NULLIF 函数的作用：

讯享网SELECT NULLIF(1, 2), NULLIF(2, 2) FROM employee WHERE emp_id = 1; 

查询返回的结果如下：

NULLIF(1,2)|NULLIF(2,2) -----------|----------- 1| | 

因为 1 不等于 2，所以查询结果中的第 1 列返回了数字 1。因为 2 等于 2，所以查询结果中的第 2 列返回了空值。

NULLIF 函数的一个常见用途是防止除零错误，例如：

讯享网-- 除零错误 SELECT * FROM employee WHERE 1 / 0 = 1; -- 避免除零错误 SELECT * FROM employee WHERE 1 / NULLIF(0 , 0) = 1; 

第 1 个查询语句中的除数为 0，因此会产生除零错误（MySQL 和 SQLite 可能不会提示错误）。第 2 个查询语句使用了 NULLIF 函数将除数 0 转换为空值，1 除以空值的结果为空值，不会产生错误。

COALESCE 函数

COALESCE (expr1, expr2, expr3, …)函数接受一个输入参数的列表，返回第 1 个非空的参数。如果所有的参数都为空，则返回空值。例如，以下查询返回了“人力资源部”员工的全年收入：

SELECT emp_name AS "员工姓名", salary*12 + bonus AS "全年收入", salary*12 + COALESCE(bonus, 0) AS "全年收入" FROM employee WHERE dept_id = 2; 

查询返回的结果如下：

讯享网员工姓名|全年收入 |全年收入 -------|---------|--------- 诸葛亮 |.00 |.00 黄忠 | | 96000.00 魏延 | | 90000.00 

查询结果中的第 2 列没有对 bonus 中的空值进行处理，导致“黄忠”和“魏延”的全年收入为空。第 3 列通过 COALESCE 函数将 bonus 中的空值转换为 0，返回了正确的全年收入。

我们也可以使用 COALESCE 函数实现空值的自定义排序：

SELECT emp_name AS "员工姓名", COALESCE(bonus, 0) AS "奖金" FROM employee WHERE dept_id = 2 ORDER BY COALESCE(bonus, 0); 

其中，ORDER BY 子句中的 COALESCE 函数用于将 bonus 为空的数据转换为 0，从而实现了空值排在最前的效果。查询返回的结果如下：

讯享网员工姓名|奖金 ------|------- 黄忠 | 0 魏延 | 0 诸葛亮 |8000.00 

COALESCE 函数对于空值的处理比搜索 CASE 表达式更加简练。

专有函数

除标准的 CASE 表达式外，一些数据库还提供了专有的扩展函数。

MySQL 提供了一个 if(expr1, expr2, expr3) 函数，如果表达式 expr1 的结果为真（不等于 0 或者不是空值），返回表达式 expr2 的值；否则，返回表达式 expr3 的值。例如：

-- MySQL SELECT IF(1<2, '1<2', '1>=2') AS result; 

查询返回的结果如下：

讯享网result ------ 1<2 

MySQL 和 SQLite 提供了 IFNULL(expr1, expr2)函数，返回第 1 个非空的参数值，等价于只有两个参数的 COALESCE 函数。例如：

-- MySQL 和 SQLite SELECT emp_name AS "员工姓名", salary*12 + IFNULL(bonus, 0) AS "全年收入" FROM employee WHERE dept_id = 2; 

该查询返回了“人力资源部”员工的全年收入。

Oracle 提供了一个 decode 函数，可以实现类似于简单 CASE 表达式的功能。该函数的语法如下：

讯享网DECODE(expression, value1, result1, value2, result2, ...[, default ]) 

函数执行时依次比较表达式 expression 与 valueN 的值。如果找到相等的值，则返回对应的 resultN；如果没有找到任何相等的值，则返回默认值 default；如果没有指定默认值，则返回空值。

前文中的简单 CASE 表达式示例可以使用 decode 函数实现：

-- Oracle SELECT emp_name, DECODE(dept_id, 1, '行政管理部', 2, '人力资源部', 3 ,'财务部', 4, '研发部', 5, '销售部', 6, '保卫部', '其他部门' ) AS "部门名称" FROM employee; 

Oracle 还提供了 NVL(expr1, expr2) 函数以及 NVL2(expr1, expr2, expr3) 函数。其中，NVL(expr1, expr2) 函数返回第 1 个非空的参数值，等价于只有两个参数的 COALESCE 函数。例如：

讯享网-- Oracle SELECT emp_name AS "员工姓名", salary*12 + NVL(bonus, 0) AS "全年收入" FROM employee WHERE dept_id = 2; 

该查询同样返回了“人力资源部”员工的全年收入。

NVL2(expr1, expr2, expr3) 函数包含 3 个参数，如果第 1 个参数不为空，返回第 2 个参数的值；否则，返回第 3 个参数的值。例如：

-- Oracle SELECT emp_name AS "员工姓名", NVL2(bonus, salary*12 + bonus, salary*12) AS "全年收入" FROM employee WHERE dept_id = 2; 

Microsoft SQL Server 提供了 ISNULL (expr1, expr2)函数，返回第 1 个非空的参数值，等价于只有两个参数的 COALESCE 函数。例如：

讯享网-- Microsoft SQL Server SELECT emp_name AS "员工姓名", salary*12 + ISNULL(bonus, 0) AS "全年收入" FROM employee WHERE dept_id = 2; 

该查询同样返回了“人力资源部”员工的全年收入。

Microsoft SQL Server 和 SQLite 还提供了一个 IIF(boolean_expression, true_value, false_value) 函数，如果表达式 boolean_expression 的结果为真，返回表达式 true_value 的值，否则返回表达式 false_value 的值。例如：

-- Microsoft SQL Server 和 SQLite SELECT IIF(1<2, '1<2', '1>=2') AS result; 

查询返回的结果和上面的 IF 函数示例相同。