Różnice pomiędzy funkcjami ISNULL i COALESCE

Funkcje ISNULL oraz COALESCE wspierają nas przy pracy z wartościami NULL. Obie te funkcje spełniają tę sama rolę lecz ich zachowanie się różni. Poniżej opiszę te funkcje oraz przybliżę różnice występujące pomiędzy nimi.

Pierwszą omawianą funkcją będzie ISNULL.

ISNULL

Składnia:

ISNULL ( check_expression , replacement_value )

Argumenty:

check_expression – Wyrażenie dowolnego typu, które jest sprawdzane czy jego wartość jest NULL.

replacement_value – Wyrażenie, które jest zwracane w przypadku gdy wartość  wyrażenia check_expression jest NULL.

Funkcja ta przyjmuje tylko dwa parametry.

Funkcja ta zwraca check_expression w przypadku gdy jego wartość jest różna od NULL a w przeciwnym przypadku zwraca wartość  replacement_value.

 

z1

Rysunek 1 Funkcja ISNULL z pierwszym pustym argumentem

W powyższym kodzie wartość pierwszego argumentu jest NULL, więc funkcja zwraca wartość drugiego argumentu czyli w tym przypadku 2.

 

z2Rysunek 2 Wynik działania

 

Zarówno w przypadku gdy oba argumenty nie przyjmują wartości NULL

 

z3

Rysunek 3 Funkcja ISNULL – podane wartości dwóch argumentów

 

jak i w przypadku gdy drugi argument przyjmuje wartość NULL

 

z5

Rysunek 4 Funkcja ISNULL – drugi argument pusty

 

zawsze jest zwracana pierwsza wartość argumentu (w tym przypadku to 5)

 

z6

Rysunek 5 Wynik

 

Jeśli oba argumenty będą miały wartość NULL funkcja ta zwróci wartość NULL.

 

z7

Rysunek 6 Funkcja ISNULL – dwa puste argumenty

 

Wyrażenie replacement_value musi być typu który umożliwia niejawne konwertowanie do typu check_expression, w przeciwnym przypadku zostanie wygenerowany błąd.

 

z8

Rysunek 7 Funkcja ISNULL – niezgodne typy argumentów

 

Powyższy kod T-SQL wygeneruje błąd:

 

z9

Rysunek 8 Funkcja ISNULL – wygenerowany komunikat błędu konwersji

 

No i na koniec jeden najważniejszy problem związany z działaniem funkcji ISNULL. Należy pamiętać, że zwracany typ danych jest narzucany przez pierwszy argument.

 

z10

Rysunek 9 Funkcja ISNULL- uwaga na typy

 

W powyższym przykładzie pierwszy argument ma wartość NULL, ale należy pamiętać że jego typ to varchar(3). Funkcja zwróci wartość argumentu drugiego, lecz tutaj uwaga. Pomimo zgodności typów należy zauważyć, że drugi  argument jest typu varchar(5) a zwracana wartość przyjmie typ varchar(3). Stąd wynik to ‘abc’ a nie ‘abcde’.

Jak to obejść ? . Należy użyć jawnej konwersji:

 

z11

Rysunek 10 Funkcja ISNULL – rozwiązanie problemu różnych typów

 

lub użyć funkcji COALESCE.

 

COALESCE

Składnia:

COALESCE ( expression [ ,…n ] )

Argumenty:

expression [ ,…n ] – lista argumentów do sprawdzenia

Cechy funkcji to:

  • Funkcja ta przyjmuje więcej niż dwa argumenty, jest ona  zgodne ze standardem ISO/ANSI SQL
  • Przynajmniej jeden argument w funkcji musi być różny od NULL.
  • Zwraca ona wartość pierwszego napotkanego parametru o wartości różnej od NULL.

Funkcja ta odpowiada wyrażeniu CASE przedstawionemu poniżej:

 

z12

Rysunek 11 Odpowiednik funkcji COLASESCE jako CASE

 

W przeciwieństwie do funkcji ISNULL zwracany typ danych nie jest narzucany przez pierwszy argument.

 

z14

Rysunek 12 Działanie funkcji COALESCE

 

Należy jednak pamiętać, że również w funkcji tej muszą zostać zachowane zasady niejawnej konwersji typów danych.

 

z15

Rysunek 13 Funkcja COALESCE – Niezgodność typów

 

W przedstawionym kodzie zostanie wygenerowany błąd:

 

z16

Rysunek 14 Funkcja COALESCE – wygenerowany komunikat błędu niezgodności typów

 

W przypadku gdy wszystkie argumenty funkcji COALESCE przyjmują wartość  NULL funkcja ta zwraca wartość NULL

 

Linki:

http://msdn.microsoft.com/library/ms184325.aspx

http://msdn.microsoft.com/en-US/library/ms190349.aspx

DBBS