compare two null values in sql

If you compare two values and either of them can be null or one of them null equal (=) operator does not work. Equal operator is not null sensitive. To compare the two you have to changed null to valid string value and then apply = operator.

declare @v1 varchar(20)
declare @v2 varchar(20)

if (isnull(@v1,'0') = isnull(@v2,'0'))
select 'Match Found'
else
select 'Match not found'

The above code demonstrates the correct way of comparing null values.

if (@v1 = @v2)
select 'Match Found'
else
select 'Match not found'

the above code will always return false if either end is null or both ends are null. Therefore this is wrong syntax.

If you are comparing more than oen value between two tables, you can use SQL Intersect as well

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s