by Piotr Pelczar | Databases, Planeta, Solutions, SQL in , , , ,

MySQL: how to convert NULL to 0 number/int

Im więcej nietypowych rzeczy programuję, tym więcej nietypowych problemów musze pokonać. Co powiecie na sumę 2 liczb, z których jedna jest wartością NULL powstałą w wyniku działania SUM() lub pochodnych, gdzie nie odnaleziono żadnego rekordu.

Badamy:

SELECT 1+2+3
>> 6

SELECT 1+2+NULL
>> NULL

SELECT COALESCE( NULL, 0 )
>> 0

Zatem analogicznie do powyższego przykładu:

UPDATE users SET user_points = user_points + COALESCE((SELECT SUM( ... ) WHERE ...), 0)

Punkty użytkownika już zawsze będą się sumowały poprawnie 🙂

 

10 thoughts on “MySQL: how to convert NULL to 0 number/int”

  1. Tytuł nieprawidłowy. Nie dokonujesz konwersji, tylko zwracasz coś w zastępstwie. I nie dotyczy to MySQL tylko języka SQL 😉

    SQL: How to return 0 (zero) instead of NULL value when SUM is used.

    Ponadto, podałeś funkcję COALESCE a nie opisałeś jej. Bardziej logicznym przykładem byłby ten pochodzący z dokumentacji:

    >> SELECT COALESCE(description,short_description,'(none)’) […]

    No i preferowane jest „(SELECT COALESCE(MAX(points),0) […])” 🙂 – wydaje mi się, że jest bardziej logiczne.

    1. @Łukasz, niestety, dane w zapytaniu są dodawane do wartości pola, a nie jako nowy rekord, więc każde dodanie jakiejlkowiek wartości NULL skasuje dotychczasową wartość i ustawi ją na 0, co chcemy pominąć. Jeżeli jest NULL, nie ma być dodawana żadna wartość do pola (czyli wystarczy dodać 0) i pozostawić ją niezmienioną. Stąd te komplikacje 🙂

    1. @Łukasz, tak. Natomiast jeżeli nie istnieją żadne rekordy spełniające warunki SUM() nie zadziała, bowiem nie można sumować wartości nieistniejących rekordów. W takim wypadku zwracana jest wartość NULL, co koliduje w późniejszym UPDATE.

      SUM([DISTINCT] expr)
      Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values of expr.

      SUM() returns NULL if there were no matching rows.

      http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum

  6. Jakie źródło wiedzy polecasz do nauki tak zaawansowanych zapytań?

  7. @Seba – dokumentacja MySQL? 😀

    @Athlan – ciekawy art, na co dzień używam MSSQL-a gdzie używam do tego celu funkcji ISNULL, w MySQL funkcja ISNULL działa inaczej i tu właśnie przydaje się COALESCE.

  8. W MSSQL jest ISNULL, a w MySQL IFNULL(expr1,expr2)

    Ewentualnie, gdy jest potrzeba użycia większej ilości warunków można użyć:

    CASE (SELECT…)
    WHEN NULL THEN 0
    [WHEN [NOT NULL] THEN …]
    END

Comments are closed.