Coalesce Vs Case

Coalesce vs Case

COALESCE() is literally shorthand for a CASE statement, they will perform identically.

However, as podiluska mentioned, ISNULL() can be occasionally faster than a CASE statement, but it's likely to be a miniscule increase as these functions are very unlikely to bottleneck your procedure.

Read here more about the performance differences.

COALESCE or CASE more efficient and/or standard

COALESCE is essentially a shorthanded CASE statement.

Both are exactly the same.

There is also ISNULL in SQL Server (differs in other DBMSs), but that's actually a non-standard feature and is actually more limited that COALESCE.

Difference between COALESCE and CASE IS NOT NULL

Under most circumstances, the two are identical. I think the standard defines COALESCE(X, Y) as:

(CASE WHEN X IS NOT NULL THEN X ELSE Y END)

Under most circumstances, this does exactly what you expect.

There is one difference. One interpretation of the standard is the X is evaluated twice when the value is not NULL. This makes no difference under most circumstances (in most cases, the value would be cached anyway).

Postgres does not do this though. It implements a more reasonable interpretation of the standard. In other words, the two are not exactly identical in Postgres, because Postgres is smart enough to evaluate the first expression once, whether it is NULL or not.

You can see the effect if you have a volatile function (one whose value changes). In Postgres, the following returns "x" and "y" about the same number of times:

select v.x, coalesce(case when random() > 0.5 then 'x' end, 'y')
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) v(x);

Equivalent code in SQL Server returns "y" half the time and "x" and NULL a quarter of the time each -- because SQL Server treats them the two constructs as exactly equivalent and evaluates the first argument twice under some circumstances.

You can see this in the db<>fiddles here and here.

However, SQL

coalesce and a case statement - explanation?

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.

Coalesce different with case

Your query with the first and second will reproduce the same result, But you are wrong understanding the Coalesce concept.

Definition in Documentation Postgresql

The COALESCE function returns the first of its arguments that is not
null. Null is returned only if all arguments are null.

So it means it will return the first argument that is not null, it is not like case statement with condition like true or false

Let's try with example :

select coalesce(null, 1)

It will return 1 like the query you show, or

select coalesce(null, null, 1)

It will return 1 too even 1 in the arg_3 and how about there are 2 value not null?

select coalesce(null, 1, 2)

It will return 1. Why? Like in the documentation said "returns the first of its arguments that is not null" so when there is 2 value not null the first argument have not null value will get return

You can check this demo and try :

Demo<>Fiddle

Hope it helps

Avoid coalesce() or case when in where clause

The expression

WHERE col = COALESCE(@var,col) 

says "if @var is NULL, return TRUE (since col always equals col)" A different approach is:

WHERE @var is NULL or col = @var

If @var is NULL, then the OR condition short-circuits (never needs to do the second part) If @var is NOT NULL, then the second part gets executed.

DB2 Performance CASE vs COALESCE

The performance of case versus coalesce() just will not make a difference to a query that is joining three large tables. Such queries are dominated by the time for reading and matching the rows in the table.

By the way, the two are not exactly the same. If you have NULL values in users.Fname, then the case logic would keep them but the coalesce() logic would fill in the values from the other table.

Your criterion should be clarity of expression. Because you think the case makes more sense, I would suggest you go with that.

HQL - COALESCE or CASE on LEFT JOIN

Assuming HQL supports coalesce (appears as such when doing a quick search), then you can use coalesce like this:

    select coalesce(table1.state, table2.state, 'unknown') as state
    from table1
    left join table 2
      on table2.id = table1.id

The coalesce will grab the first non-null value.

---

Related Topics