Efficiency of is null v\s nullif

hi ,
i am querying for data in which i compare nulls . And null = null for my
scenario. Thus i need to either use extra where conditions in the query to
check if the two compared fields are nulls. This i can do either by using is
null or nullif function. Which of the two options will be more efficient for
SQL to execute considering large data scenario?
eg:
Use NULLIF function
where (nullif(col1,col2) is null and nullif(col2,col1) is null
where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
Use IS NULL construct
where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1 <>
col2) or (col1 is null and col2 is not null) or (col1 is not null and col2
is null))prabhakar wrote:
> hi ,
> i am querying for data in which i compare nulls . And null = null for
> my scenario. Thus i need to either use extra where conditions in the
> query to check if the two compared fields are nulls. This i can do
> either by using is null or nullif function. Which of the two options
> will be more efficient for SQL to execute considering large data
> scenario? eg:
> Use NULLIF function
> where (nullif(col1,col2) is null and nullif(col2,col1) is null
> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
>
> Use IS NULL construct
> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1
> <> col2) or (col1 is null and col2 is not null) or (col1 is not null
> and col2 is null))
Not sure I understand. You have two WHERE clauses in each example. Can
you post the real SELECTs you are comparing. You can also check the
execution plans to see which one is cleaner.
David Gugick
Imceda Software
www.imceda.com|||I am comparing two fields in a table to check if they are not equal.This can
be done in following two ways
1) select * from tablefornull where ((col1 <> col2) or (col1 is null and
col2 is not null) or (col1 is not null and col2 is null))
2) select * from tablefornull where not(nullif(col1,col2) is null and
nullif(col2,col1) is null)
which one will be more efficient? The qeury execution plan is same for both
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uJFAyhzEFHA.2572@.tk2msftngp13.phx.gbl...
> prabhakar wrote:
> Not sure I understand. You have two WHERE clauses in each example. Can you
> post the real SELECTs you are comparing. You can also check the execution
> plans to see which one is cleaner.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||prabhakar wrote:
> I am comparing two fields in a table to check if they are not
> equal.This can be done in following two ways
> 1) select * from tablefornull where ((col1 <> col2) or (col1 is
> null and col2 is not null) or (col1 is not null and col2 is null))
> 2) select * from tablefornull where not(nullif(col1,col2) is null
> and nullif(col2,col1) is null)
> which one will be more efficient? The qeury execution plan is same
> for both
If the execution plan is the same, they will run exactly the same. I'm
guessing you are assuming for this exercise that NULL = NULL. Is that
correct?
How about this one:
Select * from tablefornull where isnull(col1, -999) = isnull(col2, -999)
David Gugick
Imceda Software
www.imceda.com|||prabhakar,
The performance difference in the actual processing of an individual row
not differ much between the two syntaxis. What could make a real
difference is using a syntax in a way that the optimizer can use indexes
so only the relevant rows need to be processed. Unfortunately, both
syntaxis will not achieve that.
But why do you want to compare NULLs? NULL is intended to mean something
like "unknown". In any case, the purpose of using NULLs is that one
row's NULL is not (never) equal to another row's NULL, by definition.
That is the definition that the ANSI SQL committee gave it. If you
reject that idea, you could take a look at "SET ANSI_NULLS OFF" in BOL,
and see if that works for you.
If you acknowledge the 'proper' use of NULLs, then you should never use
something like "nullif(col2,col1) is null". If there are some NULLs that
you want to treat as a values, then you could replace them with an
actual value, for example a predefined 'special' value. For varchar
columns this value could be '**Empty'. For int columns this could be
-2,147,483,648, etc.
After that you can use the following query. It is more complex than your
original query, but is likely to perform better.
SELECT *
FROM MyTable T1
WHERE NOT EXISTS (
SELECT 1
FROM MyTable T2
WHERE T2.KeyColumn = T1.KeyColumn
AND T2.Col1=T2.Col2
)
Hope this helps,
Gert-Jan
prabhakar wrote:
> hi ,
> i am querying for data in which i compare nulls . And null = null for my
> scenario. Thus i need to either use extra where conditions in the query to
> check if the two compared fields are nulls. This i can do either by using
is
> null or nullif function. Which of the two options will be more efficient f
or
> SQL to execute considering large data scenario?
> eg:
> Use NULLIF function
> where (nullif(col1,col2) is null and nullif(col2,col1) is null
> where not(nullif(col1,col2) is null and nullif(col2,col1) is null)
> Use IS NULL construct
> where ((col1 = col2) or (col1 is null and col2 is null)) where ((col1 <>
> col2) or (col1 is null and col2 is not null) or (col1 is not null and col2
> is null))