Let us suppose the following table (e.g. a result of several inner join statements):

id | column_1 | column_2
------------------------
 1 |  1       | 
 2 |  2       | 2
 3 |          | 3

Which you could for example get from the following statement:

select a.id, t1.column_1, t2.column_2
from a
left join t1 on a.id = t1.id
left join t2 on a.id = t2.id

Now, if i'd like to sum up t1.column_1 and t2.column_2 as follows

select 
    a.id, 
    t1.column_1, 
    t2.column_2,
    (t1.column_1 + t2.column_2) as cumulated
from a
left join t1 on a.id = t1.id
left join t2 on a.id = t2.id

The reslut will look as follows:

id | column_1 | column_2 | cumulated
------------------------------------
 1 |  1       | NULL     | NULL
 2 |  2       | 2        | 4
 3 |  NULL    | 3        | NULL

My question basically is: is there a way to typecast NULL into 0 in order to do some math?

I have tried CONVERT(t1.column_1, SIGNED) and CAST(t1.column_1 as SIGNED), but a NULL stays a NULL.

推荐答案

Use IFNULL(column, 0) to convert the column value to zero.

Alternatively, the COALESCE function will do the same thing: COALESCE(column, 0), except

  1. COALESCE is ANSI-compliant, IFNULL is not
  2. COALESCE takes an arbitrary number of columns/values and will return the first non-null value passed to it.

Mysql相关问答推荐

MySQL - 事务绑定多个存储过程调用和回滚的简单方法?

如果其中一个表为空,则 mysql 中的查询会给出 0 个结果

过滤值为0时如何在MySQL查询中设置条件?

根据 Power Query 中的条件替换值

MySQL 在第一个匹配行后停止搜索 N 行(不是 LIMIT)

try 在 .map 中使用 Favorite 选项卡进行react .我无法更改 mysql 表上的布尔类型

当用它的别名替换 (MAX(s.salary) - MIN(s.salary) 它将不起作用.. 为什么?

判断一列中的多个值并返回值 1 或 0

在一个 SQL 查询中对同一列中相同类型的不同值求和

Golang Gorm没有从关联表中检索数据

终止空闲的mysql连接

MySQL 如何处理并发插入?

If else on WHERE 子句

MySQL时区更改?

为什么数据库行元组中的整数具有L后缀?

在 Yii2 中执行原始 SQL 查询?

将 Blob 转换为字节数组的最简单方法

MySQL术语约束与外键的区别?

在osx的命令行中使用Mysql-找不到命令?

带有 WHERE 子句的 MySql 内连接