[sql][Tips][EN] Let’s get started COALESCE command for WHERE query if data is null or not

[sql][Tips][EN] Let’s get started COALESCE command for WHERE query if data is null or not

For SQL command, before I check data entries are null or not null, I may set a lot of query in WHERE command more and more.

Let’s use COALESCE command, so that the command use for checking data entries if the data is null, it use first parameter or if the data is not null, it use instead of second parameter.

Structure:

COALESCE(‘DATA ENTRY’,SAME FIELD)

For example:

--to create variable for test query

DECLARE @DataForTest VARCHAR(20) = NULL

SELECT LotNoX

FROM [Packing]

WHERE LotNoX = COALESCE(@DataForTest,LotNoX)

***pattern 1 COALESCE

SELECT LotNoX

FROM [Packing]

WHERE LotNoX = CASE WHEN @DataForTest IS NULL THEN LotNoXELSE @DataForTest END

***pattern 2 CASE WHEN…THEN…ELSE…END

IF @DataForTest IS NOT NULL

     BEGIN

          SELECT LotNoX

          FROM [Packing]

          WHERE LotNoX = @DataForTest 

     END

***pattern 3 IF

Finally, when I use COALECE command, It makes me more short statement and easy to write the query.

Keywords:

SQL, COALESCE, Microsoft SQL Server