Как заменить null на 0 в sql
Чтобы заменить значение NULL на 0 в SQL, можно использовать функцию COALESCE . Эта функция принимает несколько аргументов и возвращает первый не NULL аргумент. Если все аргументы NULL , функция вернет NULL . Вот пример использования COALESCE для замены значений NULL на 0 :
В этом запросе column_name — имя столбца, значения которого нужно заменить, а table_name — имя таблицы, в которой находится столбец. Функция COALESCE заменит все значения NULL в столбце на 0 . Если значение столбца не NULL , то функция вернет его без изменений.
Также можно использовать оператор IS NULL для проверки на NULL и замены его на 0 . Вот пример:
Этот запрос также заменит значения NULL на 0 . Если значение столбца не NULL , то запрос вернет его без изменений.
# NULLs
In SQL Server, NULL represents data that is missing, or unknown. This means that NULL is not really a value; it’s better described as a placeholder for a value. This is also the reason why you can’t compare NULL with any value, and not even with another NULL .
# COALESCE ()
COALESCE () Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL .
Although ISNULL() operates similarly to COALESCE(), the ISNULL() function only accepts two parameters — one to check, and one to use if the first parameter is NULL. See also ISNULL , below
SQL Server ISNULL Function
Summary: in this tutorial, you will learn how to use the SQL Server ISNULL() function to replace NULL with a specified value.
SQL Server ISNULL() function overview
The SQL Server ISNULL() function replaces NULL with a specified value. The following shows the syntax of the ISNULL() function:
The ISNULL() function accepts two arguments:
- expression is an expression of any type that is checked for NULL .
- replacement is the value to be returned if the expression is NULL . The replacement must be convertible to a value of the type of the expression .
The ISNULL() function returns the replacement if the expression evaluates to NULL . Before returning a value, it implicitly converts the type of replacement to the type of the expression if the types of the two arguments are different.
In case the expression is not NULL , the ISNULL() function returns the value of the expression .
SQL Server ISNULL() function examples
Let’s take some examples of using the ISNULL() function.
Using SQL Server ISNULL() function with the numeric data example
This example uses the ISNULL() function to return the second argument because the first argument is NULL :
Here is the output:
Using SQL Server ISNULL() function with character string example
The following example uses the ISNULL() function to return the string ‘Hello’ because it is the first argument and not NULL :
Using SQL Server ISNULL() function to replace NULL values with meaningful values
First, create a new table named divisions that stores athlete’s divisions by ages:
Second, insert some rows into the divisions table:
Third, query data from the divisions table:
The following picture shows the output:
If a division does not require minimum age, the min_age column will have NULL . Similarly, if a division does not require maximum age, the max_age column will also have NULL .
Last, use the ISNULL() function to convert NULL in the min_age column to 0 and NULL in the max_age column to 99 :
The following picture shows output:
In this tutorial, you have learned how to use the SQL Server ISNULL() function to replace NULL with a specified value.
4 Ways to Replace NULL with a Different Value in MySQL
In MySQL, sometimes you don’t want NULL values to be returned as NULL . Sometimes you want NULL values to be returned with a different value, such as “N/A”, “Not Applicable”, “None”, or even the empty string “”.
Fortunately there are several ways to do this in MySQL.
- The IFNULL() function
- The COALESCE() function
- The IF() function combined with the IS NULL (or IS NOT NULL ) operator
- The CASE expression combined with the IS NULL (or IS NOT NULL ) operator
Examples of these options are below.
Sample Data
First, let’s grab some sample data:
So we have three NULL values and three non-NULL values.
The IFNULL() Function
Given its name, this is probably the most obvious option for replacing NULL values in MySQL. This function is basically the equivalent of ISNULL() in SQL Server.
The IFNULL() function allows you to provide two arguments. The first argument is returned only if it is not NULL. If it is NULL, then the second argument is returned instead.
Here’s an example of using IFNULL() against our sample data set:
Here, we simply replaced NULL values with N/A .
The COALESCE() Function
This function is similar to the IFNULL() function, but slightly different. This function adheres to the ANSI SQL standard, and it is widely deployed across various RDBMSs .
The way it works is, you provide as many arguments as you need. COALESCE() will then return the first non- NULL value in the list, or NULL if there are no non- NULL values.
So we get exactly the same result as previously.
However, the difference with this function is that, as mentioned, you can provide a list of arguments. The COALESCE() function will take whichever is the first non-NULL value.
So for example, we could add NULL as the first argument and place None before N/A and look what happens:
It skipped the first NULL as expected, then it skipped any NULL values in the TaskCode column, before settling on None . The N/A value didn’t get used in this case because None came first and it’s a non-NULL value.
The IF() Function Combined with IS NULL/IS NOT NULL
The IS NULL and IS NOT NULL operators allow you to test for NULL values, and present a different value depending on the outcome.
We can use these operators inside the IF() function, so that non-NULL values are returned, and NULL values are replaced with a value of our choosing.
So the same result as with the IFNULL() and COALESCE() functions.
And of course, we could swap IS NOT NULL with IS NULL . If we do that, we would need to swap the subsequent arguments too:
The CASE Expression Combined with IS NULL/IS NOT NULL
Another way to do it is to use the CASE expression:
As with the previous example, this could be rewritten to use IS NULL instead of IS NOT NULL :