coalesce and a case statement - explanation?

Question

I'm working on a query that doesn't seem to be doing what it's supposed to since being transferred from one SSRS server to another, and the following line in part of the where statement appears to be where the difference is, or at least from what I can find.

where COALESCE(field, -1) = CASE field WHEN 1 THEN 0 ELSE -1 END

I know it's probably a bit generic but can anyone shed any light on what this may be doing? I've read up on coalesce and gather that it finds the first non-null value from the specified list of fields, but I don't understand what the '-1' does there.

I hope it's not too general a question and that someone can give me a clue as to what it may be doing

Answer 1

Without context it's difficult to give a truly helpful answer. At first glance it looks as though it could be rewritten much more simply from this:

WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END

to this:

WHERE COALESCE(@field, -1) = -1

If that is true then basically you are saying that if the field is null or the field equals -1 then the condition is true otherwise it's false.

Here are some tests to try to prove this:

-- Original
DECLARE @field INT
SELECT 1 WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END
SET @field = -1
SELECT 1 WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END
SET @field = 0
SELECT 1 WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END
SET @field = 1
SELECT 1 WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END
SET @field = 2
SELECT 1 WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END
SET @field = 3
SELECT 1 WHERE COALESCE(@field, -1) = CASE @field WHEN 1 THEN 0 ELSE -1 END

--Rewritten
DECLARE @field INT
SELECT 1 WHERE COALESCE(@field, -1) = -1
SET @field = -1
SELECT 1 WHERE COALESCE(@field, -1) = -1
SET @field = 0
SELECT 1 WHERE COALESCE(@field, -1) = -1
SET @field = 1
SELECT 1 WHERE COALESCE(@field, -1) = -1
SET @field = 2
SELECT 1 WHERE COALESCE(@field, -1) = -1
SET @field = 3
SELECT 1 WHERE COALESCE(@field, -1) = -1

Both sets of queries in this test give the same results, but as I said without context and realistic test data it's difficult to know if there was a reason why the query was written in the way that it originally was.

Here is another example from a different perspective, using a LEFT JOIN:

DECLARE @MainTable AS TABLE(ident INT)
DECLARE @PossibleNullTable AS TABLE(mainIdent INT, field INT)

INSERT INTO @MainTable(ident) VALUES(1)
INSERT INTO @MainTable(ident) VALUES(2)
INSERT INTO @MainTable(ident) VALUES(3)
INSERT INTO @MainTable(ident) VALUES(4)
INSERT INTO @MainTable(ident) VALUES(5)

INSERT INTO @PossibleNullTable(mainIdent, field) VALUES(1,-1)
INSERT INTO @PossibleNullTable(mainIdent, field) VALUES(1,1)
INSERT INTO @PossibleNullTable(mainIdent, field) VALUES(1,0)

INSERT INTO @PossibleNullTable(mainIdent, field) VALUES(2,0)
INSERT INTO @PossibleNullTable(mainIdent, field) VALUES(3,1)
INSERT INTO @PossibleNullTable(mainIdent, field) VALUES(5,-1)

--Original
SELECT *
FROM @MainTable mt
LEFT JOIN @PossibleNullTable pnt
    ON mt.ident = pnt.mainIdent
WHERE COALESCE(field, -1) = CASE field WHEN 1 THEN 0 ELSE -1 END

--Original Result 
ident   mainIdent   field
1       1           -1
4       NULL        NULL
5       5           -1

--Rewritten
SELECT *
FROM @MainTable mt
LEFT JOIN @PossibleNullTable pnt
    ON mt.ident = pnt.mainIdent
WHERE COALESCE(field, -1) = -1

--Rewritten Result 
ident   mainIdent   field
1       1           -1
4       NULL        NULL
5       5           -1

Again both queries in this test give the same results.

Answer 2

This is a bit complex expression used in where clause, that suggests

use field column, replace NULL values with -1 & compare all these values of field column with the case expression such as,

here at first, we have to consider that there is no null value in field column as we have replaced all null with -1 using coalesce().

then in case statement, if the values is 1 then it is replace by 0. So 0 is checked with coalesce(field,-1), if it is also 0, then expression is true else false.

Similar for field value -1 using case.

Answer 3

the first non-null value from the specified list of fields

This means the list of fields between the parenthesis. For instance:

COALESCE(col1,col2,col3,-1)

means that if col1 is not null then use this, else check col2. If col2 is null then check col3. If that is null too then use -1 as the value.

In your example, COALESCE(field, -1) is equivalent to ISNULL(field, -1)

In my example COALESCE(col1,col2,col3,-1) is equivalent to ISNULL(ISNULL(ISNULL(col1, col2), col3), -1)