SQL从入门到出门 第10篇 CASE：SQL中的条件表达式

本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的CASE表达式，以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

SQL 是一种声明性的语言，它没有提供其他编程语言中的条件语句（if-then-else 或者 switch）。但是，SQL 支持 CASE 表达式，可以基于一个条件列表返回不同的结果值，它更像是一种支持嵌套的三元表达式（? :）。

CASE 表达式支持两种形式：简单 CASE 表达式和搜索 CASE 表达式。另外，还存在一些简写形式的 CASE 函数，以及不同数据库产品特有的扩展。

简单 CASE 表达式

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

CASE expression
  WHEN expression_1 THEN result_1
  WHEN expression_2 THEN result_2
  ...
  [ELSE default_result]
END

简单 CASE 表达式比较CASE后的表达式与WHEN列表中的表达式的值，找到第一个匹配的值（=），返回后面相应的结果；如果没有匹配值，返回ELSE子句的值；如果此时没有指定ELSE子句，返回空值。

举个例子：

SELECT e.first_name,
       e.last_name,
       e.job_id,
       CASE e.job_id
         WHEN 'AD_PRES' THEN 'President'
         WHEN 'AD_VP'   THEN 'Administration Vice President'
         WHEN 'IT_PROG' THEN 'Programmer'
         ELSE 'Other job'
       END
  FROM employees e;

以上查询根据不同的职位编号，显示相应的职位名称，其中的ELSE确保了不会返回空值。

简单 CASE 表达式仅仅比较两个值是否相等，然后返回相应的值。如果我们想要基于更加复杂的条件进行判断，例如某个值是否为空，某个值是否大于指定的数值，甚至使用子查询的结果进行判断，都需要使用更加强大的搜索 CASE 表达式。

搜索 CASE 表达式

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

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_1
  ...
  [ELSE default_result]
END

表达式按照顺序计算WHEN子句中的条件，找到第一个结果为 True 的分支，返回相应的结果；如果没有任何条件为真，返回ELSE中的默认值；如果此时没有指定ELSE子句，返回空值。

同样来看一个例子：

SELECT e.first_name,
       e.last_name,
       e.salary,
       CASE
         WHEN e.salary < 5000  THEN 'Low'
         WHEN e.salary < 10000 THEN 'Middle'
         WHEN e.salary < 15000 THEN 'High'
         ELSE 'Top'
       END AS salary_level
  FROM employees e;

我们根据薪水的取值范围显示相应的描述，结果如下：

上文中的简单 CASE 表达式示例也可以写成搜索形式：

SELECT e.first_name,
       e.last_name,
       e.job_id,
       CASE 
         WHEN e.job_id = 'AD_PRES' THEN 'President'
         WHEN e.job_id = 'AD_VP'   THEN 'Administration Vice President'
         WHEN e.job_id = 'IT_PROG' THEN 'Programmer'
         ELSE 'Other job'
       END
  FROM employees e;

CASE 表达式除了可以用于SELECT列表，也可以出现在其他SQL子句中，例如WHERE条件、GROUP BY分组、ORDER BY排序等。看一个例子：

SELECT CASE
         WHEN e.salary < 5000  THEN 'Low'
         WHEN e.salary < 10000 THEN 'Middle'
         WHEN e.salary < 15000 THEN 'High'
         ELSE 'Top'
       END AS salary_level,
       MIN(e.salary) min_salary,
       MAX(e.salary) max_salary
  FROM employees e
 GROUP BY CASE
            WHEN e.salary < 5000  THEN 'Low'
            WHEN e.salary < 10000 THEN 'Middle'
            WHEN e.salary < 15000 THEN 'High'
            ELSE 'Top'
          END;

以上查询按照薪水范围显示不同级别，并且按照级别分组显示最低薪水和最高薪水值。

缩写 CASE 表达式

除了以上两种形式的 CASE 表达式之外，SQL 还定义了两个与NULL值相关的缩写 CASE 表达式（函数）：nullif 和 coalesce。

NULLIF 函数需要2个参数，如果第一个参数等于第二个参数，返回第一个参数的值；否则，返回NULL值：

NULLIF(expression_1, expression_2)

它的等价 CASE 表达式如下：

CASE
  WHEN expression_1 = expression_2 THEN NULL
  ELSE expression_1
END

来个简单的示例：

SELECT NULLIF(1, 2),
       NULLIF(1, 1)
  FROM employees e;

以上查询第 1 列返回数值 1，第 2 列返回空值。

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

value1 / NULLIF(value2 , 0)

COALESCE 函数接受一个参数列表，并且返回第一个非空的参数值；如果所有参数都为空，返回空值：

COALESCE(expression_1, expression_2, expression_3, ...)

它的等价 CASE 表达式如下：

CASE
WHEN expression_1 IS NOT NULL THEN expression_1
WHEN expression_2 IS NOT NULL THEN expression_2
WHEN expression_3 IS NOT NULL THEN expression_3
...
END

再来个简单的示例：

SELECT e.first_name,
       e.last_name,
       COALESCE(e.commission_pct, 0)
  FROM employees e;

如果员工存在佣金百分比，返回该值；否则，显示为 0。

专有扩展函数

除了SQL 标准中定义的表达式之外，不同的数据库产品还实现了一些类似的扩展函数。

数据库	CASE 表达式	描述
Oracle	OK	Oracle 还提供了以下函数（参见后文示例）： NVL(expr1, expr2) NVL2(expr1, expr2, expr3) DECODE(expr, search, result, ...[, default ])
MySQL	OK	MySQL 还提供了以下函数（参见后文示例）： IF(expr1, expr2, expr3) IFNULL(expr1, expr2)
SQL Server	OK	SQL Server 还提供了以下函数（参见后文示例）： ISNULL ( check_expression , replacement_value )
PostgreSQL	OK
Db2	OK	Db2 还提供了以下函数（参见后文示例）： NVL(expr1, expr2, ...) NVL2(expr1, expr2, expr3) DECODE(expr, search, result, ...[, default ]) IFNULL(expr1, expr2) – only for z/OS
SQLite	OK	SQLite 还提供了以下函数（参见后文示例）： IFNULL(expr1, expr2)

首先，来看NVL(expr1, expr2)函数，它用于返回第一个非空的参数值。Oracle 和 Db2 都支持该函数，区别在于 Oracle 只接受两个参数，DB2 可以接受多个参数。

-- For Oracle and Db2
SELECT e.first_name,
       e.last_name,
       NVL(e.commission_pct, 0)
  FROM employees e;

-- For Db2 only
SELECT e.first_name,
       e.last_name,
       NVL(e.commission_pct, e.salary, 0)
  FROM employees e;

接下来是NVL2(expr1, expr2, expr3)函数，它包含三个参数。如果第一个参数不为空，返回第二个参数的值；否则，返回第三个参数的值。Oracle 和 Db2 都支持该函数。

-- For Oracle and Db2
SELECT e.first_name,
       e.last_name,
       NVL2(e.commission_pct, e.salary + e.salary * e.commission_pct / 100, e.salary)
  FROM employees e;

Oracle 和 Db2 还提供了 DECODE函数：

DECODE(expr, search, result, ...[, default ])

其中的 search 和 result 成对出现，函数依次比较 expr 与 search 的值，如果找到相等的值，返回对应的 result；如果没有匹配到任何结果，返回 default 值；如果此时没有提供 default，返回空值。

-- For Oracle and Db2
SELECT e.first_name,
       e.last_name,
       DECODE(e.job_id, 'AD_PRES', 'President', 'AD_VP', 'Administration Vice President', 'Other job')
  FROM employees e;

如果可能匹配的值太多，DECODE 函数不便于阅读和理解，推荐使用 CASE 表达式。另外，MySQL 中的 DECODE 函数是一个解密函数。

MySQL 提供了类似于 NVL2 的 IF 函数：

IF(expr1, expr2, expr3)

它接受三个参数。如果第一个参数为真（expr1 <> 0 并且 expr1 不为空），返回第二个参数的值；否则，返回第三个参数的值。

-- For MySQL only
SELECT IF(1<2, '1<2', '1>=2'), IF(NULL, 'Y', 'N');
-- result is: 1<2        N

MySQL、SQLite 和 Db2（ only for z/OS） 提供了类似于 NVL 的IFNULL(expr1, expr2)函数。它接受两个参数，如果 expr1 不为空，返回 expr1 的值；否则，返回 expr2 的值。

-- For Oracle and SQLite and Db2 for z/OS
SELECT e.first_name,
       e.last_name,
       IFNULL(e.commission_pct, 0)
  FROM employees e;

SQL Server 提供了类似于 NVL 的ISNULL ( check_expression , replacement_value )。它接受两个参数，如果 check_expression 不为空，返回 expr1 的值；否则，返回 replacement_value 的值。

-- For SQL Server only
SELECT e.first_name,
       e.last_name,
       ISNULL(e.commission_pct, 0)
  FROM employees e;

最后，在 SQL: 2003 增加了两个针对简单 CASE 表达式的可选扩展。但是，目前本文包含的六种主流数据库都不支持这种扩展，可能是因为使用搜索 CASE 表达式完全能满足这种需求。

第一种扩展就是允许在WHEN子句中使用比较运算符：

-- For no implementation
SELECT e.first_name,
       e.last_name,
       CASE e.salary
         WHEN < 5000  THEN 'Low'
         WHEN < 10000 THEN 'Middle'
         WHEN < 15000 THEN 'High'
         ELSE 'Top'
       END AS salary_level
  FROM employees e;

第二种扩展就是允许在WHEN子句中使用逗号分隔的表达式列表：

-- For no implementation
SELECT e.first_name,
       e.last_name,
       CASE e.job_id
         WHEN 'AD_PRES', 'AD_VP' THEN 'President'
         WHEN 'SA_MAN', 'SA_REP' THEN 'Sales People'
         WHEN e.job_id = 'IT_PROG' THEN 'Programmer'
         ELSE 'Other job'
       END
  FROM employees e;

欢迎留言探讨！