SQL中case when函数和if函数的区别

在SQL语句中，经常会使用case when函数或者if函数作为条件判断，需要注意的是：

1. if函数只能用作单个条件判断，case when函数可以用作多个条件判断。
2. 在使用case when函数的时候一定要加上else，否则不满足case when的其它数据都将被null值所替代。

基本语法

if函数基本语法

if (条件判断，符合条件值，不符合条件值)

case when函数基本语法

case 
when 条件1 then 符合条件值 
when 条件2 then 符合条件值
...
else 符合条件值 
end

数据检验

准备两张源数据表。

mysql> select * from student;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | zhangsan |  19 |
|  2 | lisi     |  20 |
|  3 | wangwu   |  21 |
+----+----------+-----+
3 rows in set

mysql> select * from school;
+----+----------+
| id | school   |
+----+----------+
|  1 | school01 |
|  3 | school03 |
+----+----------+
2 rows in set

使用if函数判断

mysql> select t1.id,t1.name,if(t2.school is null,'NOTFOUND',t2.school) as school from student t1 left join school t2 on t1.id = t2.id;
+----+----------+----------+
| id | name     | school   |
+----+----------+----------+
|  1 | zhangsan | school01 |
|  2 | lisi     | NOTFOUND |
|  3 | wangwu   | school03 |
+----+----------+----------+
3 rows in set

使用case when函数判断加上else

mysql> select t1.id,t1.name,case when t2.school is null then 'NOTFOUND' else t2.school end as school from student t1 left join school t2 on t1.id = t2.id;
+----+----------+----------+
| id | name     | school   |
+----+----------+----------+
|  1 | zhangsan | school01 |
|  2 | lisi     | NOTFOUND |
|  3 | wangwu   | school03 |
+----+----------+----------+
3 rows in set

使用case when函数判断不加else

mysql> select t1.id,t1.name,case when t2.school is null then 'NOTFOUND' end as school from student t1 left join school t2 on t1.id = t2.id;
+----+----------+----------+
| id | name     | school   |
+----+----------+----------+
|  1 | zhangsan | NULL     |
|  2 | lisi     | NOTFOUND |
|  3 | wangwu   | NULL     |
+----+----------+----------+
3 rows in set

使用建议

尽量使用if函数替代case when函数，如果必须使用case when函数，一定要考虑else的情况。

打赏