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

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

  • автор:

Замена 0 на Null

В запросах Insert Values и Update, константа 0 или параметр со значением 0 заменяется на Null при записи в поле, если поле является ссылкой и поддерживает запись Null.

Работает только для константы 0. Значения выражений и запросов результат которых равен 0 не преобразовывается. Например 0+0 или даже (0).

Это замена не работает в хранимых процедурах.

Procedure OnCreate; Var Name : String; Group : Integer; Begin Name := 'test'; Group := 0; // При выполнении этого запроса Group будет заменено на Null (Insert Into Clients(Name, Group) Values(:Name, :Group)); // При выполнении этого запроса Group будет заменено на Null (Update Clients Set Group=:Group Where Name=:Name); // При выполнении этого запроса 0 будет заменено на Null (Update Clients Set Group=0 Where Name=:Name); // При выполнении этих запросов Group НЕ БУДЕТ заменено на Null (Update Clients Set Group=(:Group) Where Name=:Name); (Update Clients Set Group=:Group+0 Where Name=:Name); (Insert Into Clients(Name, Group) Select :Name, :Group); End;

Это сделано, что бы в программе не работать со значениями Null. К сожалению значения Null обязательны для корректной работы внешних ключей и JOIN-ов.

При извлечении значения Null из базы данных, он автоматически заменяется на 0, 0c, » или NullDatetime.

Procedure OnCreate; Var Name : String; Group : Integer; Begin Name := 'test'; // При выполнении этого запроса Null будет заменено на 0 Group := (Single Group From Clients Where Name=:Name); End;

Можно сделать замену 0 на Null и для выражений, но это снизит производительность.

Функция ISNULL (Transact-SQL)

Заменяет значение NULL указанным замещающим значением.

Синтаксис

ISNULL ( check_expression , replacement_value ) 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

check_expression
Выражение, которое необходимо проверить на равенство значению NULL. Аргумент check_expression может быть любого типа.

replacement_value
Выражение, возвращаемое, если check_expression имеет значение NULL. Аргумент replacement_value должен иметь тип, который может быть неявно преобразован в тип check_expression.

Типы возвращаемых данных

Возвращает тип, совпадающий с типом выражения check_expression. Если в аргументе check_expression предоставлено литеральное значение NULL, возвращает тип данных replacement_value. Если в аргументе check_expression предоставлено литеральное значение NULL, а аргумент replacement_value не задан, возвращает int.

Замечания

Возвращается значение check_expression, если это выражение не равно NULL. В противном случае возвращается значение replacement_value. Если типы являются разными, то тип replacement_value неявно преобразуется в тип check_expression. Значение replacement_value может усекаться, если значение replacement_value длиннее, чем check_expression.

Для возврата первого значения, отличного от NULL, используйте функцию COALESCE (Transact-SQL).

Примеры

А. Использование функции ISNULL с функцией AVG

Следующий пример демонстрирует расчет среднего значения веса всех продуктов. Все записи со значением NULL в столбце 50 таблицы Weight заменяются значением Product .

USE AdventureWorks2022; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO 
-------------------------- 59.79 (1 row(s) affected) 

B. Использование функции ISNULL

Следующий пример производит выборку описания, процента скидки, минимального и максимального количества для всех специальных предложений из базы AdventureWorks2022 . Если максимальное количество для отдельного специального предложения равно NULL, отображаемое значение MaxQty в результирующем наборе заменяется на 0.00 .

USE AdventureWorks2022; GO SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity' FROM Sales.SpecialOffer; GO 
Description DiscountPct MinQty Максимальное количество
Без скидки 0.00 0 0
Оптовая скидка 0.02 11 14
Оптовая скидка 0.05 15 4
Оптовая скидка 0.10 25 0
Оптовая скидка 0,15 41 0
Оптовая скидка 0,20 61 0
Mountain-100 Cl 0,35 0 0
Sport Helmet Di 0.10 0 0
Road-650 Overst 0,30 0 0
Mountain Tire S 0,50 0 0
Sport Helmet Di 0,15 0 0
LL Road Frame S 0,35 0 0
Touring-3000 Pr 0,15 0 0
Touring-1000 Pr 0,20 0 0
Half-Price Peda 0,50 0 0
Mountain-500 Si 0,40 0 0

(16 row(s) affected)

C. Проверка значений NULL в предложении WHERE

Не используйте для поиска значений NULL выражение ISNULL, вместо него следует использовать выражение IS NULL. В следующем примере выполняется поиск всех продуктов, имеющих значение NULL в столбце веса. Заметьте, что между словами IS и NULL стоит пробел.

USE AdventureWorks2022; GO SELECT Name, Weight FROM Production.Product WHERE Weight IS NULL; GO 

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

D. Использование функции ISNULL с функцией AVG

В приведенном ниже примере рассчитывается среднее значение веса всех продуктов в образце таблицы. Все записи со значением NULL в столбце 50 таблицы Weight заменяются значением Product .

-- Uses AdventureWorks SELECT AVG(ISNULL(Weight, 50)) FROM dbo.DimProduct; 
-------------------------- 52.88 

Д. Использование функции ISNULL

В приведенном ниже примере функция ISNULL используется для поиска значений NULL в столбце MinPaymentAmount и отображения значения 0.00 для соответствующих строк.

-- Uses AdventureWorks SELECT ResellerName, ISNULL(MinPaymentAmount,0) AS MinimumPayment FROM dbo.DimReseller ORDER BY ResellerName; 

Здесь приводится частичный результирующий набор.

ResellerName MinimumPayment
A Bicycle Association 0,0000
A Bike Store 0,0000
A Cycle Shop 0,0000
A Great Bicycle Company 0,0000
A Typical Bike Shop 200,0000
Acceptable Sales & Service 0,0000

F. Использование функции IS NULL для проверки на значение NULL в предложении WHERE

В приведенном ниже примере выполняется поиск всех продуктов, имеющих значение NULL в столбце Weight . Заметьте, что между словами IS и NULL стоит пробел.

-- Uses AdventureWorks SELECT EnglishProductName, Weight FROM dbo.DimProduct WHERE Weight IS NULL; 

Замена значений NULL на 0 в MySQL: безопасные методы

Если необходимо заменить значения null на 0 в MySQL, следует использовать функции COALESCE() либо IFNULL():

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

SELECT COALESCE(column_name, 0) FROM table_name; -- COALESCE подходит для поиска первого ненулевого значения

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

SELECT IFNULL(column_name, 0) FROM table_name; -- IFNULL идеально подходит для простой замены null

После выполнения данных запросов все значения null мгновенно будут заменены на 0 в выдаваемом результирующем наборе данных.

Встреча с чудовищем null: краткое введение

В MySQL вы можете встретить два союзника в борьбе со значениями null :

  1. Функция COALESCE(): как надёжный помощник, всегда имеет запасной вариант.
  2. Функция IFNULL(): быстро реагирует, если что-то пошло не так.

Обе функции могут быть использованы в запросах SELECT для управления ‘null’ и для обеспечения однозначности результатов.

COALESCE: универсальная функция, всегда готовая прийти на помощь

Функция COALESCE() последовательно просматривает свои аргументы и выбирает первое не-null значение. Это позволяет более гибко обрабатывать данные:

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

SELECT COALESCE(potential_null_1, potential_null_2, potential_null_3, 0) AS safe_column FROM table_name; -- Этот подход обеспечивает надёжность результата

В этом случае, если potential_null_1 равно null , функция проверит potential_null_2 и так далее, возвращая 0, если все аргументы окажутся null .

IFNULL: когда даже один null может стать проблемой

Функция IFNULL() работает просто: принимая два аргумента, она возвращает второй, в случае, если первый аргумент — null . Это прямолинейно и быстро:

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

SELECT IFNULL(column_with_suspected_nulls, 0) AS safe_column FROM table_name; -- С помощью этого подхода вы сразу переключаетесь на запасной план

Обновление записей. велика сила, велика и ответственность

Если вы решите заменить все null на нули в вашей базе данных, примените update-запросы. Но помните о своей ответственности: значения null могут нести важный смысл, который легко потерять, заменив их на абсолютные нули:

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

UPDATE table_name SET target_column = 0 WHERE target_column IS NULL; -- Это как надеть на всех одинаковые парики, когда не всегда это уместно.

Визуализация

Процесс замены null на 0 можно сравнить с реконструкцией заброшенных зданий (��️) в отреставрированные объекты (��):

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

Таблица базы данных = Квартал Столбец с NULL = Заброшенные здания Столбец с 0 = Отреставрированные здания

В терминах SQL это будет выглядеть так:

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

SELECT COALESCE(AbandonedBuilding, 0) as RefurbishedBuilding FROM Neighbourhood;

А результат будет следующим:

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

До: [��, ��️, ��, ��️, ��️] После: [��, ��, ��, ��, ��]

Функция COALESCE() здесь выступает в роли аллегорического градостроителя, придающего ценность любому объекту, будь он старым или отреставрированным.

Погружение в детали: рекомендации и предостережения

Семантика данных имеет значение

Будьте внимательны при замене null на 0 в глобальном масштабе, так как это может исказить истинные значения данных. Null иногда несёт важную информацию, например, об отсутствии данных, что отличает его от нулевого значения.

Понимание контекста использования

В зависимости от ситуации, IFNULL() и COALESCE() могут быть использованы взаимозаменяемо. Но всегда учитывайте контекст:

  • Для замены в одной колонке, IFNULL() используется из-за более простого синтаксиса.
  • Если необходимо проверить несколько потенциально null-значений, выбирайте COALESCE() .

Влияние на производительность

Не забывайте о том, что любая замена null может повлиять на производительность, особенно при обрабоке больших массивов данных. Функции IFNULL() и COALESCE() используют дополнительные ресурсы, что следует учесть при оптимизации запросов.

Полезные материалы

  1. Функции сравнения и операторы — Официальная документация MySQL по COALESCE() и её применению.
  2. Оптимизация запросов – mysql, ifnull против coalesce, что быстрее? – Stack Overflow — Обсуждение производительности функций IFNULL и COALESCE .
  3. MySQL | Функция ISNULL( ) – GeeksforGeeks — Руководство по функции ISNULL() в MySQL.
  4. MySQL – Управление дубликатами — Гид по обработке дубликатов записей в MySQL.
  5. Руководство по оператору CASE в MySQL — Углублённое объяснение оператора CASE в MySQL, полезного для работы с null.

Как составить sql запрос на замену null на 0?

Просто если в каком-то из столбцов стоит значение null, то в suma тоже получается null. Помогите решить проблему.

  • Вопрос задан более трёх лет назад
  • 5786 просмотров

Комментировать
Решения вопроса 1
Стандарт предполагает использование COALESCE:

select COALESCE(price, 0) + COALESCE(price2, 0) + COALESCE(price3,0) as suma from prices

Ответ написан более трёх лет назад
Нравится 2 6 комментариев
DERBIAGLOBALISTO @DERBIAGLOBALISTO Автор вопроса
DERBIAGLOBALISTO @DERBIAGLOBALISTO Автор вопроса
А вот сервак тормозит жёстко из-за этого coalesce. (не знаю может и не из-за него)

DERBIAGLOBALISTO, покажите реальный запрос «было-стало». Запрос как в вопросе тормозить из-за этого не должен

DERBIAGLOBALISTO @DERBIAGLOBALISTO Автор вопроса

select * from (select @n := @n + 1, uc.id_user as iduser, (select u.login from users u where u.id = uc.id_user) as login, (select up.level_mutagen from users_pancer up where up.id_user = uc.id_user) as level_mutagen, (select up.level_pancers_1 from users_pancer up where up.id_user = uc.id_user) as lp1, (select up.level_pancers_2 from users_pancer up where up.id_user = uc.id_user) as lp2, (select up.level_pancers_3 from users_pancer up where up.id_user = uc.id_user) as lp3, (select up.level_pancers_4 from users_pancer up where up.id_user = uc.id_user) as lp4, round(uc.health / 4) + coalesce((select lpm.protect from _levels_pancer_mutagen lpm where lpm.level = level_mutagen), 0) + coalesce((select lpp1.protect from _levels_pancer_pancers lpp1 where lpp1.level = lp1), 0) + coalesce((select lpp2.protect from _levels_pancer_pancers lpp2 where lpp2.level = lp2), 0) + coalesce((select lpp3.protect from _levels_pancer_pancers lpp3 where lpp3.level = lp3), 0) + coalesce((select lpp4.protect from _levels_pancer_pancers lpp4 where lpp4.level = lp4), 0) as param_sum from users_combat uc order by param_sum desc, login asc) x where iduser = ? limit 1

DERBIAGLOBALISTO @DERBIAGLOBALISTO Автор вопроса

unfilled, уверен что можно было как-то лучше запрос написать, но я не спец в sql. Суть запроса в том чтобы получить порядковый номер записи юзера в mysql.

DERBIAGLOBALISTO, если вот эти подзапросы:

(select up.level_pancers_1 from users_pancer up where up.id_user = uc.id_user)
(select lpp2.protect from _levels_pancer_pancers lpp2 where lpp2.level = lp2)

всегда возвращают не более одной записи, можно попробовать переписать запрос так:

select * from ( select @n := @n + 1, uc.id_user as iduser, u.login, up.level_mutagen , up.level_pancers_1 as lp1 , up.level_pancers_2 as lp2 , up.level_pancers_3 as lp3 , up.level_pancers_4 as lp4, round(uc.health / 4) + coalesce(lpm.protect, 0) + coalesce(lpp1.protect, 0) + coalesce(lpp2.protect, 0) + coalesce(lpp3.protect, 0) + coalesce(lpp4.protect, 0) as param_sum from users_combat uc join users_pancer up on uc.id_user = up.id_user join users u on u.id = uc.id_user left join _levels_pancer_mutagen lpm on lpm.level = up.level_mutagen left join _levels_pancer_pancers lpp1 on lpp1.level = up.level_pancers_1 left join _levels_pancer_pancers lpp2 on lpp2.level = up.level_pancers_2 left join _levels_pancer_pancers lpp3 on lpp3.level = up.level_pancers_3 left join _levels_pancer_pancers lpp4 on lpp4.level = up.level_pancers_4 order by param_sum desc, login asc )x where iduser = ? limit 1

Если не поможет — надо уже предметно разбираться, смотреть какие у вас таблицы, связи, данные, индексы, каким получается план выполнения.

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

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