Operator for Nullable Column Comparison - by Greg Low - Australia

Status : 

  Duplicate<br /><br />
		This item appears to be a duplicate of another existing Connect or internal item.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 307476 Comments
Status Resolved Workarounds
Type Suggestion Repros 6
Opened 10/29/2007 11:08:18 PM
Access Restriction Public
Primary Feedback Item 286422


It is very common to need to compare the values in two nullable columns, without changing the ansi nulls setting. For example, it is common to have to write:

WHERE (a.SomeColumn IS NULL AND b.SomeColumn IS NULL) OR (a.SomeColumn = b.SomeColumn)

Given how common this is (particularly for some forms of optimistic concurrency checks that aren't based on rowversions), there should be a simple way to express the intent. I also presume the optimizer could also potentially take advantage of knowing the real intent of the statement.
Sign in to post a comment.
Posted by John.Bevan on 1/4/2013 at 6:30 AM
I think it would be great if you could extend the existing IS statement - i.e. instead of just having "where ColumnOrVariable is null" you can use it as "where ColumnOrVariable1 is ColumnOrVariable2"; thus it behaves as equals when the second column/variable has a value, and as an is null when the value is null.

declare @t1 table (id bigint identity(1,1), A nvarchar(10), B int)
declare @t2 table (id bigint identity(1,1), A nvarchar(10), B int)

insert @t1
select 'A', 1
union all select 'B', 2
union all select 'C', null
union all select null, 1
union all select null, 3
union all select null, null

insert @t2
select 'A', 1
union all select 'B', 2
union all select 'C', null
union all select null, 1
union all select null, 3
union all select null, null

--null <> null
select *
from @t1 o
full outer join @t2 t
on o.A = t.A
and o.B = t.B

--null is null
select *
from @t1 o
full outer join @t2 t
on o.A is t.A
and o.B is t.B
Posted by xor88 on 6/22/2011 at 12:54 PM
It is strange that this very desirable feature does not yet exist although the query processor already supports it (see http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx for proof).
Posted by Microsoft on 6/7/2011 at 3:58 PM
Hello Greg and others,
Just a quick comment to indicate that this item is indeed active in our bug database. For some reason due to some issue with the synchronization process between our database and connect system the "Active" state is not getting reflected. So please be assured that this one and other suggestion filed by Steve Kass will be looked at in the future.

Umachandar, SQL Programmability Team
Posted by Microsoft on 1/22/2008 at 2:36 PM
Thank you for your feedback. Implementing the DISTINCT syntax is definitely desirable and has already been suggested, see http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286422
Unfortunately, due to the high number of new features, this enhancement did not make it into SQL Server 2008, and therefore we have to defer it to our next major release.
Best regards,
Posted by Mark Yudkin on 11/12/2007 at 2:41 AM
Why invent a proprietary syntax when ANS SQL 99 already defines one that does the job? This requirement needs to be rephrased as "Implement the ANS 99 Standard DISTINCT Predicate".

Section 8.13 of ISO 8075-2-1999 specifies:
i) ‘‘X IS DISTINCT FROM Y’’ is false if either:
1) X and Y are the null value, or
2) X = Y according to Subclause 8.2, ‘‘<comparison predicate>’’.
ii) Otherwise, ‘‘X IS DISTINCT FROM Y’’ is true.
Posted by Razvan Socol on 11/2/2007 at 12:30 PM
Here is a similar sugestion, regarding the "IS [NOT] DISTINCT" predicate from the ISO standard SQL-1999:
Posted by Tony Wright on 10/30/2007 at 3:55 PM
I much prefer the clarity of WHERE a.SomeColumn ?= b.SomeColumn to a function
Posted by w0nnie on 10/30/2007 at 3:21 PM
or the option proposed on SQLDownUnder:

IsEqualOrNull(a.someColumn, b.someColumn)
Posted by Greg Low - Australia on 10/29/2007 at 11:18 PM
Perhaps we could even make a play on the "int?" used by .net generics and make it:

WHERE a.SomeColumn ?= b.SomeColumn