SQL Server : COALESCE kreatywne użycie i NULL = NULL

COALESCECOALESCE akceptuje serie wartości z listy wyników, które mogą być puste (NULL) potem ona zwraca pierwszą nie pustą wartość z tej listy.

Taka funkcjonalność daje wiele kreatywnych użyć w  bazie SQL Server.

Oto proste przykłady.

Tabelka PeopleNames zawiera kolumnę “Middle Name”, która może być pusta w końcu nie każda osoba ma drugie imię. Naszym celem jest dodanie wszystkie napisów uwzględniając przy tym drugie imię.

Dodawanie napisów COALESCE

Skrypt tworzący tabelkę znajduję się poniżej.

CREATE TABLE [dbo].[PeopleNames](
	[Id] [int] NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_PeopleNames] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Tabelkę trzeba uzupełnić danymi.

INSERT INTO [dbo].[PeopleNames]
           ([Id]
           ,[FirstName]
           ,[MiddleName]
           ,[LastName])
     VALUES
           (1,'Cezary',NULL,'Walenciuk'),
           (2,'Stefan','Viktor','Kowalski'),
           (3,'Franko','Cyryl','Domanowski'),
           (4,'Zdzisław',NULL,'Bohaterowicz')

Funkcja COALESCE pozwala nam wstawić naszą wartość w przypadku wystąpienia elementu pustego. Gdy drugie imię będzie puste zostanie wstawiony pusty znak.

SELECT  FirstName + ' ' +COALESCE(MiddleName,'')+ ' ' + LastName FROM PeopleNames

Co pozwoli nam dodać wszystkie wartości kolumn bez żadnych problemów.

Operacja dodawania napisów, gdy jeden z parametrów jest NULL zwróci NULL.Jakiekolwiek wyrażenie matematyczne na typach liczbowych zwróci NULL, jeśli jeden z parametrów jest NULL.

SELECT  FirstName + ' ' +MiddleName + ' ' + LastName FROM PeopleNames

Dlatego bez użycia funkcji  COALESCE bezpośrednie dodanie kolumn przy pustym drugi imieniu zwróci NULL.

Jak zauważyłeś przy poprawnym wyniku, gdy drugie imię jest puste występuje jeden  znak spacji za dużo. Jednak to nie jest duży problem wystarczy tylko trochę zmodyfikować zapytanie.

SELECT  FirstName + COALESCE(' ' + MiddleName,'')+ ' ' + LastName FROM PeopleNames

Teraz zawsze występuje jeden znak spacji bez względu na to, czy drugie imię jest puste, czy nie.

Matematyka z COALESCE

Oto drugi przykład z tabelą Package.

Tabela ta określa ilość zapłaty za przesyłkę, która jest zależna od wagi paczki od dystansu oraz od kosztu specjalnego. Wszystkie te pola jednak mogą  być puste. Czyli cena za przesyłkę może być liczona  poprzez kombinację tych pól w zależności od tego, czy są puste, czy nie.

Jak widać tabelka też zawiera pola bitowe które określają z jakiej kombinacji te pola będą liczone ,ale oczywiście nie będziemy liczyć ceny za pomocą wyrażenie IF i ELSE (to nie C#).

CREATE TABLE [dbo].[Package](
	[PackagedId] [int] NOT NULL,
	[WeightKg] [decimal](18, 2) NULL,
	[PricePerKg] [decimal](18, 2) NULL,
	[DistanceKm] [decimal](18, 2) NULL,
	[PricePerKm] [decimal](18, 2) NULL,
	[AdditionalCost] [decimal](18, 2) NULL,
	[CalculateWeight] [bit] NOT NULL,
	[CalculateDistance] [bit] NOT NULL,
	[CalculateAdditionalCost] [bit] NOT NULL,
 CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED 
(
	[PackagedId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Jak więc wygląda rozwiązanie przy użyciu wyrażenia COALESCE . Uzupełnimy tabelkę i się przekonajmy.

INSERT INTO [dbo].[Package]
           ([PackagedId]
           ,[WeightKg]
           ,[PricePerKg]
           ,[DistanceKm]
           ,[PricePerKm]
           ,[AdditionalCost]
           ,[CalculateWeight]
           ,[CalculateDistance]
           ,[CalculateAdditionalCost])
     VALUES
           (1,NULL,NULL,NULL,NULL,10.50,0,0,1),
           (2,15.5,5.25,NULL,NULL,5.50,1,0,1),
           (3,25.5,7.25,975,0.25,5.50,1,1,1),
           (4,NULL,NULL,1342,0.25,NULL,0,1,0),
           (5,NULL,NULL,2128.5,0.15,20.63,0,1,1),
           (6,NULL,NULL,538.7,0.30,15.25,0,1,1),
           (7,55.5,4.25,NULL,NULL,NULL,1,0,0)

Wyrażenie mnożenia zwróci NULL, jeśli chociaż jeden z parametrów też jest NULL co oznacza ,że całe wyrażenie mnożenia możemy umieścić wewnątrz funkcji COALESCE. Z logiki tej tabelki jasno wynika ,że jeśli wartość ceny za kilogram jest puste to  kolumna  kilogramy też powinna być pusta. Czyli nie powinnyśmy uwzględniać ceny za kilogramy w dodawaniu, jeśli chociażby jedna z tych kolumn ma pustą wartość.

Gdy wyrażenie mnożenia będzie NULL funkcja  COALESCE zwróci wartość zero.

SELECT 
      PackagedId,
      COALESCE(WeightKg * PricePerKg,0)+
      COALESCE(DistanceKm * PricePerKm,0)+
      COALESCE(AdditionalCost,0) AS Pay
FROM dbo.Package

W ten sposób obliczamy cena przesyłkę bez względu na to, które pola są puste ,a które nie.

Te wyrażenie może być przydatne w kilku miejscach w twojej aplikacji wciąż nie jest to jednak wydajne rozwiązanie.

W takim wypadku warto stworzyć kolumnę, która ma już i będzie miała przy nowych rekordach  skalkulowaną tą wartość.

Ta wartość jest dynamicznie obliczana przez SQL więc nie trzeba jej aktualizować przy zmianie innych kolumn. Dzieje się to automatycznie.

ALTER TABLE dbo.Package
ADD Payment AS
      COALESCE(WeightKg * PricePerKg,0)+
      COALESCE(DistanceKm * PricePerKm,0)+
      COALESCE(AdditionalCost,0)

Teraz zwykłe wyrażenie SELECT zwróci cena całkowitą za przesyłkę paczki.

WHERE z COALESCE

COALESCE ma też oczywiście inne praktyczne użycia niż dodawanie różnych wartości.

Oto procedura, która zwróci imiona osób z poprzedniej tabelki. Dzięki COALESCE parametry @FirstName oraz @LastName mogą być puste co daje dużo elastyczność.

CREATE  PROCEDURE [dbo].[uspReadPeopleNames]
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL
AS
BEGIN
	SELECT [Id]
		  ,[FirstName]
		  ,[MiddleName]
		  ,[LastName]
	 FROM [dbo].[PeopleNames]
	 WHERE [FirstName] LIKE COALESCE(@FirstName,[FirstName]) OR
	 [LastName] LIKE COALESCE(@LastName,[LastName])
	 
END

Gdy parametr nie zostanie podany COALESC wstawi w wrażeniu logicznym tą samą wartość kolumny z którą jest on porównywany. Takie wyrażenie  zawsze zwróci prawdę przynajmniej dla kolumn bez wartości  pustych.

Warto też zwrócić uwagę na różnice warunków OR  i AND, gdyż w obecnym przypadku gdy podamy tylko jeden parametr wciąż otrzymamy wszystkie wyniki.  W końcu zwracam wyniki pozytywne dla obu warunków.

Dopiero po podaniu dwóch parametrów wybór zostanie ograniczony.

EXEC	@return_value = [dbo].[uspReadPeopleNames]
		@FirstName = N'F%',
		@LastName =  N'W%'

EXEC	@return_value = [dbo].[uspReadPeopleNames]

Jak widać, jeśli nie podamy żadnych parametrów zostaną zwrócone wszystkie wyniki.

Problem NULL = NULL

A jak wybrać pole “MiddleName”. Dla przypomnienia NULL w bazie danych oznacza wartość nieokreśloną. Nie określoność nie określoności nie równa . Czyli przyrównanie NULL = NULL zawsze zwróci wartość logiczną false.

W takim wypadku funkcja COALESCE w niczym nam nie pomoże. Wręcz nawet utrudni zapytanie. Musimy uwzględnić scenariusz pustej kolumny oraz pustego parametru. W takim wypadku  lepiej  zastosować proste wyrażenie logiczne.

ALTER PROCEDURE [dbo].[uspReadPeopleNames]
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@MiddleName VARCHAR(50) = NULL
AS
BEGIN
	SELECT [Id]
		  ,[FirstName]
		  ,[MiddleName]
		  ,[LastName]

	 FROM [dbo].[PeopleNames]
	 WHERE 
	 [FirstName] LIKE COALESCE(@FirstName,[FirstName]) AND
	 [LastName] LIKE COALESCE(@LastName,[LastName]) AND
	 ([MiddleName] IS NULL AND @MiddleName IS NULL) OR ([MiddleName] LIKE @MiddleName) 
END

Zmienić też operacje logiczne na AND. Teraz będą selekcją będzie uwzględniać cześć wspólną wszystkich warunków.  Pokaże wyniki dwóch zapytań dowodzących ,że wyrażenie powyżej jest dobrze napisane, ponieważ mam dziwne przeczucie ,że tak nie jest.

EXEC	@return_value = [dbo].[uspReadPeopleNames]
		@FirstName = N'C%',
		@LastName =  N'W%'

EXEC	@return_value = [dbo].[uspReadPeopleNames]
		@MiddleName = N'V%',
		@FirstName = N'S%',
		@LastName = N'K%'

Wszystko wydaje się w porządku ,ale gdy nie podamy żadnego parametru powinienem otrzymać wszystkie wyniki jednak tak nie jest. Teraz wiesz jak to fajnie być testerem swojej aplikacji.

Jak widać obecna funkcja dla parametru  MiddelName  z wartością NULL domyślnie wykluczę pola nie puste gdy nie podamy parametru. Trzeba to poprawić.

ALTER PROCEDURE [dbo].[uspReadPeopleNames]
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@MiddleName VARCHAR(50) = NULL
AS
BEGIN
	SELECT [Id]
		  ,[FirstName]
		  ,[MiddleName]
		  ,[LastName]

	 FROM [dbo].[PeopleNames]
	 WHERE 
	 [FirstName] LIKE COALESCE(@FirstName,[FirstName]) AND
	 [LastName] LIKE COALESCE(@LastName,[LastName]) AND
	 (([MiddleName] IS NULL AND @MiddleName IS NULL) OR ([MiddleName] LIKE @MiddleName) 
	 OR (@MiddleName IS NULL))
END

Sprawdź działanie procedury raz jeszcze.

EXEC	@return_value = [dbo].[uspReadPeopleNames]
EXEC	@return_value = [dbo].[uspReadPeopleNames]
		@FirstName = N'Stefan'
EXEC	@return_value = [dbo].[uspReadPeopleNames]
		@FirstName = N'Cezary'

Teraz wszystko działa jak należy.

Mechanizm OR

Jeśli z jakiegoś powodu chcesz mieć poprzedni mechanizm OR, który zwraca sumę wszystkich pozytywnych wyrażenie logiczny oto poniżej zmieniony mechanizm tego kodu.

ALTER PROCEDURE [dbo].[uspReadPeopleNames]
@FirstName VARCHAR(50) = NULL,
@LastName VARCHAR(50) = NULL,
@MiddleName VARCHAR(50) = NULL
AS
BEGIN
	SELECT [Id]
		  ,[FirstName]
		  ,[MiddleName]
		  ,[LastName]

	 FROM [dbo].[PeopleNames]
	 WHERE 
	 [FirstName] LIKE COALESCE(@FirstName,[FirstName]) OR
	 [LastName] LIKE COALESCE(@LastName,[LastName]) OR
	 (([MiddleName] IS NULL AND @MiddleName IS NULL) 
	 OR ([MiddleName] LIKE @MiddleName) 
	 OR ([MiddleName] IS NOT NULL AND  @MiddleName IS NULL))
 
END

Wypadałoby skróci te wyrażenie algebrą boola o ile to możliwe dla wydajności.

Gdy nie podam parametru MiddelName zostaną zwrócone wszystkie wyniki.

Natomiast gdy określę wszystkie parametry otrzymam ich sumę spełnionych warunków.

EXEC	@return_value = [dbo].[uspReadPeopleNames]
		@MiddleName = N'V%',
		@FirstName = N'Stefan',
		@LastName = N'Walenciuk'

Kowalski został wybrany, ponieważ jego drugie imię zaczyna się na “V” oraz jego imię to “Stefan”. Cezary został wybrany, ponieważ jego nazwisko to “Walenciuk”.