To check on NULL or 0

Question

What is faster in SQL to check value for NULL or 0

I want to have the fastest way to check is value already in table.

For example which is faster :

IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] 
     WHERE VALUE = @myVal) is null )
BEGIN
....
END
ELSE
BEGIN
....
END

or

IF ((SELECT COUNT(ID) FROM [SomeTable].[dbo].[BlockedSubscriberNumbers]
     WHERE VALUE = @myVal) > 0 )
BEGIN
....
END
ELSE
BEGIN
....
END

Also does in T-SQL plays role where the frequent accruing case must be. I mean is it will be faster that in most cases it will fail into IF block and slower if it will mostly go into ELSE.

Answer 1

Well these will actually do different things, you can't check if a NULL is greater than 0 in SQL.

What you should do is this.

    IF (ISNULL((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] 
         WHERE VALUE = @myVal), 0) > 0 )
    BEGIN
    ....
    END
    ELSE
    BEGIN
    ....
    END

And did you actually mean equals 0? Because your question states

What is faster in SQL to check value for NULL or 0

This part is in regards to Joe's comment about multiple result sets

You could do some kind of aggreate function over the Select ID using the ISNULL to determine whether or not any of the values are greater than 0 (assuming of course that all of your values are greater than 0).

In regards to figuring out if any rows contained that information per the OPs comment

IF (Select Count(ID) from [SomeTable].[dbo].[BlockedSubscriberNumbers] Where Value = @myVal) = 0 Shoud tell you if there are any Rows containing that value

Final Edit

Just use Exists

If Exists(Select ID From [SomeTable].[dbo].BlockedSubscriberNumbers] Where Values = @myVal)

Answer 2

Checking for NULL is much faster than checking for 0, but I think that, for those queries, we're talking about different things: they will produce different results.