Operator for Nullable Column Comparison by Greg Low - Australia

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

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).

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

Greg,
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,
Roman

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.

Here is a similar sugestion, regarding the "IS [NOT] DISTINCT" predicate from the ISO standard SQL-1999:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286422

I much prefer the clarity of WHERE a.SomeColumn ?= b.SomeColumn to a function

or the option proposed on SQLDownUnder:

IsEqualOrNull(a.someColumn, b.someColumn)

Perhaps we could even make a play on the "int?" used by .net generics and make it:

WHERE a.SomeColumn ?= b.SomeColumn