Search code examples
sqlt-sqlsqlperformance

Refactoring a COALESCE comparison

In the OR section of a WHERE clause I have something like this:

COALESCE(Table2.FireDate, Table1.HireDate,'06/06/2079') = ISNULL(Table3.DeathDate,'06/06/2079')

I wanted to see is there a way to avoid COALESCE call and still achieve the same result? My hope is gaining some performance if possible.

Solution

  • I don't sure that performance will be better. But you can get rid from coalesce as you want:

    declare @fireDate date = null -- '01/20/2017'
declare @hireDate date = '01/20/2017'
declare @deathDate date = '01/20/2017'

if (COALESCE(@fireDate, @hireDate, '06/06/2079') = ISNULL(@deathDate, '06/06/2079'))
begin
    select '+'
end;


if(@fireDate is null and @hireDate is null and @deathDate is null)
   or (@fireDate = @deathDate)
   or (@fireDate is null and @hireDate = @deathDate) begin
    select '+'
end;