/* Case 4: @VAR = @VAR */

declare @i int = null, @j int = null;

set ansi_nulls off;

if @i = @j

select 'True' as [Case 4, ansi_nulls OFF]

else

select 'FALSE' as [Case 4, ansi_nulls OFF];

set ansi_nulls on;

if @i = @j

select 'True' as [Case 4, ansi_nulls ON]

else

select 'FALSE' as [Case 4, ansi_nulls ON];

if (@i = @j)

or (@i is null and @j is null)

select 'True' as [Case 4, ansi_nulls ON, solution 1]

else

select 'FALSE' as [Case 4, ansi_nulls ON, solution 1];

if exists(select @i intersect select @j)

select 'True' as [Case 4, ansi_nulls ON, solution 2]

else

select 'FALSE' as [Case 4, ansi_nulls ON, solution 2];

go

/* Case 5: @VAR = COLUMN */

create table T (x int not null, j int null);

insert into T values (1, null);

declare @i int = null;

set ansi_nulls off;

if @i = (select j from T where x = 1)

select 'True' as [Case 5, ansi_nulls OFF]

else

select 'FALSE' as [Case 5, ansi_nulls OFF];

set ansi_nulls on;

if @i = (select j from T where x = 1)

select 'True' as [Case 5, ansi_nulls ON]

else

select 'FALSE' as [Case 5, ansi_nulls ON];

/* Solution that can cause bad performance */

if (@i = (select j from T where x = 1))

or (@i is null and (select j from T where x = 1) is null)

select 'True' as [Case 5, ansi_nulls ON, solution 1]

else

select 'FALSE' as [Case 5, ansi_nulls ON, solution 1];

/* Preferred solution */

if exists (select @i intersect (select j from T where x = 1))

select 'True' as [Case 5, ansi_nulls ON, solution 2]

else

select 'FALSE' as [Case 5, ansi_nulls ON, solution 2];

go

drop Table T;

go

/* Case 6: COLUMN IN (SET OF LITERALS) */

create table T (x int not null, j varchar(15) null);

insert into T values (1, null), (2, ''), (3, 'Hello world');

go

set ansi_nulls off;

select [case] = 'Case 6, ansi_nulls OFF', * from T where j in ('', null);

set ansi_nulls on;

select [case] = 'Case 6, ansi_nulls ON', * from T where j in ('', null);

/* Solution that can cause bad performance */

select [case] = 'Case 6, ansi_nulls ON, solution 1', * from T where j = '' or j is null;

/* Solution can be used if column j is not indexed */

select [case] = 'Case 6, ansi_nulls ON, solution 2', * from T where ISNULL(j, '') = '';

/* Preferred solution */

select [case] = 'Case 6, ansi_nulls ON, solution 3', * from T where j = ''

union

select [case] = 'Case 6, ansi_nulls ON, solution 3', * from T where j is null

drop Table T;

go

/* Case 7: @VAR IN (SELECT COLUMN) */

create table T (i int null)

go

insert into T values (1), (2), (null), (3)

go

declare @A int = null;

set ansi_nulls off

if @A in (select i from T) print 'case 7, ANSI_NULLS = OFF: true' else print 'case 7, ANSI_NULLS = OFF: FALSE'

set ansi_nulls on

if @A in (select i from T) print 'case 7, ANSI_NULLS = ON: true' else print 'case 7, ANSI_NULLS = ON: FALSE'

if exists (select @A intersect (select i from T)) print 'case 7, Solve: true' else print 'case 7, Solve: FALSE'

drop table T

go