Как заменить null на 0 в sql
Перейти к содержимому

Как заменить null на 0 в sql

  • автор:

Замена NULL на 0 в SQL: функция SUM и отсутствие данных

Для элегантного преобразования NULL , полученного от SQL-функции, в 0 воспользуйтесь функцией COALESCE :

Скопировать код

SELECT COALESCE(yourFunction(. ), 0);

Теперь если результат функции будет NULL , на его место подставится надежное значение 0 .

Понимание NULL и агрегатных функций в SQL

Корректное применение SQL-функций критически важно для обеспечения точности данных. Агрегатные функции вида SUM() возвращают NULL в случае отсутствия соответствующих записей. Для обеспечения более предсказуемых результатов распространено заменять NULL на 0 с помощью функции COALESCE :

Скопировать код

SELECT COALESCE(SUM(column), 0) . -- Если SUM ничего не нашла, COALESCE выручит, подставив ноль.

Как альтернативу можно использовать функцию ISNULL :

Скопировать код

SELECT ISNULL(SUM(column), 0) . -- Если в результатах SUM обнаружен NULL, функция ISNULL заменит его на ноль.

ISNULL проверяет первый аргумент на NULL , и если он является таковым, заменяет его вторым аргументом. Однако поддержка ISNULL ограничена двумя параметрами. Если целью является простое замещение NULL в SQL Server, ISNULL будет эффективной, но COALESCE предпочтительнее за свою универсальность.

Переводите возможные NULL в подзапросах в нули для учета, подобного тому, как это делается в CRM-системах:

Замена значений NULL на 0 в SQL запросе: SUM, CASE

Для замены NULL на 0 в SQL Server используйте функции COALESCE() или ISNULL() . Вот примеры их использования:

Скопировать код

-- Замена NULL с помощью COALESCE SELECT COALESCE(имя_столбца, 0) FROM имя_таблицы; -- Замена NULL с помощью ISNULL SELECT ISNULL(имя_столбца, 0) FROM имя_таблицы;

Обе функции позволяют заменить значение NULL на 0 за время выполнения запроса.

Глублый разбор ISNULL и COALESCE

Могучий ISNULL

Функция ISNULL принимает на вход два аргумента: название столбца и значение для замены NULL . Она вычисляется всего один раз за время выполнения SELECT-запроса, обеспечивая оптимальную производительность в некоторых случаях.

Скопировать код

-- Объявляю: NULL будет повсеместно замещён «нулевыми героями» SELECT ISNULL(имя_столбца, 0) FROM имя_таблицы;

Универсальность COALESCE

COALESCE принимает неограниченное количество аргументов и возвращает первое не- NULL значение из списка. Это добавляет гибкости в ваш код.

Скопировать код

-- Достучаться до значений: точно не до NULL SELECT COALESCE(столбец_один, столбец_два, 0) FROM имя_таблицы;

Но помните: COALESCE , проверяя каждый параметр, может слегка замедлить выполнение запроса.

Тюнинг производительности

ISNULL работает быстрее при больших объёмах данных, поскольку значение вычисляется единожды. В то время как COALESCE , проверяя каждый параметр по одному, может уменьшить производительность.

Избегайте NULL в агрегатных функциях

Внутри функции SUM() используйте ISNULL , чтобы значения NULL не искажали общий результат. Таким образом, вы получите корректные итоги.

Как заменить 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 :

Похожие публикации:

  1. Какой оператор sql используется для вставки новых данных в базу данных
  2. Не удалось найти объект так как он не существует или отсутствуют разрешения sql
  3. Работа с базами данных sql что это
  4. Файлы поддержки программы установки microsoft sql server 2008 что это

Как заменить значения в SQLlite таблице?

В Как правильно запрос сделать чтобы во всей таблице tab1 заменить значения NULL на 1 допустим. Перечитал, не могу понять полностью эти запросы.

Отслеживать
33.1k 2 2 золотых знака 33 33 серебряных знака 67 67 бронзовых знаков
задан 18 янв 2020 в 14:32
Рафаэль солихов Рафаэль солихов
33 5 5 бронзовых знаков
Для всех полей? Или для одного конкретного поля?
18 янв 2020 в 14:37
Думаю, примерно: UPDATE tab1 SET <название столбца>= 1 WHERE <название столбца>IS NULL
18 янв 2020 в 14:37
для всех полей на всей таблице
18 янв 2020 в 14:37
Все спасибо сработало понял теперь
18 янв 2020 в 14:47
Если мой ответ решил вашу задачу, то отметьте его как верный
18 янв 2020 в 14:48

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Ну для всех полей автоматически вы одним запросом не обновите. А вот если перечислить все поля, то тогда это делается таким запросом

UPDATE tab SET f1 = COALESCE(f1, 1), f2 = COALESCE(f2, 1), . fn = COALESCE(fn, 1); 

Здесь f1 . fn имена полей в таблице, а COALESCE встроенная функция, которая возвращает первый NOT NULL аргумент.

Т.е. для каждой записи поле будет обновлено своим же значением, если оно не равно NULL или 1 в противном случае

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *