/* Case 8: @VAR <> LITERAL */
declare
@i int
= null;
set ansi_nulls off
;
if
@i <> 1
select
'True'
as
[Case 8, ansi_nulls OFF]
else
select
'FALSE'
as
[Case 8, ansi_nulls OFF];
set ansi_nulls on
;
if
@i <> 1
select
'True'
as
[Case 8, ansi_nulls ON]
else
select
'FALSE'
as
[Case 8, ansi_nulls ON];
if
isnull(@i, 0) <> 1
select
'True'
as
[Case 8, ansi_nulls ON, solution 1]
else
select
'FALSE'
as
[Case 8, ansi_nulls ON, solution 1];
if
(@i <> 1)
or (@i is null)
select
'True'
as
[Case 8, ansi_nulls ON, solution 2]
else
select
'FALSE'
as
[Case 8, ansi_nulls ON, solution 2];
if
not exists (select
@i intersect select
1)
select
'True'
as
[Case 8, ansi_nulls ON, solution 3]
else
select
'FALSE'
as
[Case 8, ansi_nulls ON, solution 3];
go
/* Case 9: @VAR <> @VAR */
declare
@i int = null, @j int
= 1;
set ansi_nulls off
;
if
@i <> @j
select
'True'
as
[Case 9, ansi_nulls OFF]
else
select
'FALSE'
as
[Case 9, ansi_nulls OFF];
set ansi_nulls on
;
if
@i <> @j
select
'True'
as
[Case 9, ansi_nulls ON]
else
select
'FALSE'
as
[Case 9, ansi_nulls ON];
if
(@i <> @j)
or (@i is null and @j is not null)
or (@i is not null and @j is null)
select
'True'
as
[Case 9, ansi_nulls ON, solution 1]
else
select
'FALSE'
as
[Case 9, ansi_nulls ON, solution 1];
if
not exists (select
@i intersect select
@j)
select
'True'
as
[Case 9, ansi_nulls ON, solution 2]
else
select
'FALSE'
as
[Case 9, ansi_nulls ON, solution 2];
go
/* Case 10: @VAR <> COLUMN */
/* Note that a 1 in the table and a null in the variable also triggers the difference in behavior */
create table
T (x int
not null, j int
null);
insert into
T values
(1, null);
go
declare
@i int
= 1;
set ansi_nulls off
;
if
@i <> (select
j from
T where
x = 1)
select
'True'
as
[Case 10, ansi_nulls OFF]
else
select
'FALSE'
as
[Case 10, ansi_nulls OFF];
set ansi_nulls on
;
if
@i <> (select
j from
T where
x = 1)
select
'True'
as
[Case 10, ansi_nulls ON]
else
select
'FALSE'
as
[Case 10, 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 not null)
or (@i is not null and (select
j from
T where
x = 1) is null)
select
'True'
as
[Case 10, ansi_nulls ON, solution 1]
else
select
'FALSE'
as
[Case 10, ansi_nulls ON, solution 1];
/* Preferred solution */
if not exists
(select
@i intersect
(select
j from
T where
x = 1))
select
'True'
as
[Case 10, ansi_nulls ON, solution 2]
else
select
'FALSE'
as
[Case 10, ansi_nulls ON, solution 2];
go
drop Table
T;
go
/* Case 11: COLUMN NOT 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 11, ansi_nulls OFF'
, * from
T where
j not in (''
, null);
set ansi_nulls on
;
select
[case] = 'Case 11, ansi_nulls ON'
, * from
T where
j not in
(''
, null
);
select
[case] = 'Case 11, ansi_nulls ON, solution'
, * from
T where
j != ''
and j is not null
;
drop Table
T;
go
/* Case 12 and 13: @VAR NOT IN (SET OF LITERALS)/NOT (@VAR IN (SET OF LITERALS)) */
declare
@A int
= NULL
;
set ANSI_NULLS OFF
;
if
@A not in
(1, 2) print
'case 12, ANSI_NULLS = OFF: true'
else print
'case 12, ANSI_NULLS = OFF: FALSE'
;
set ANSI_NULLS ON
;
if
@A not in
(1, 2) print
'case 12, ANSI_NULLS = ON: true'
else print
'case 12, ANSI_NULLS = ON: FALSE'
;
if
@A not in
(1, 2) or @A is null print
'case 12, Solve: true'
else print
'case 12, Solve: FALSE'
;
print
''
;
set ANSI_NULLS OFF
;
if
not (@A in (1, 2)) print
'case 13, ANSI_NULLS = OFF: true'
else print
'case 13, ANSI_NULLS = OFF: FALSE'
;
set ANSI_NULLS ON
;
if
not
(@A in (1, 2)) print
'case 13, ANSI_NULLS = ON: true'
else print
'case 13, ANSI_NULLS = ON: FALSE'
;
if
not
(@A in (1, 2)) or @A is null print
'case 13, Solve: true'
else print
'case 13, Solve: FALSE'
;
go
/* Case 14 & 15: COLUMN NOT IN (SET OF LITERALS)/NOT (COLUMN IN (SET OF LITERALS)) */
create table
T (i int
null)
go
insert into
T values
(1), (2), (null), (3)
go
declare
@A int
= 7;
set ansi_nulls off
if
@A not in (select
i from
T) print
'case 14, ANSI_NULLS = OFF: true'
else print
'case 14, ANSI_NULLS = OFF: FALSE'
set ansi_nulls on
if
@A not in (select
i from
T) print
'case 14, ANSI_NULLS = ON: true'
else print
'case 14, ANSI_NULLS = ON: FALSE'
if
not exists (select
@A intersect
(select
i from
T)) print
'case 14, Solve: true'
else print
'case 14, Solve: FALSE'
print
''
;
set ANSI_NULLS OFF
;
if
not (@A in (select
i from
T)) print
'case 15, ANSI_NULLS = OFF: true'
else print
'case 15, ANSI_NULLS = OFF: FALSE'
;
set ANSI_NULLS ON
;
if
not (@A in (select
i from
T)) print
'case 15, ANSI_NULLS = ON: true'
else print
'case 15, ANSI_NULLS = ON: FALSE'
;
if
not exists (select
@A intersect
(select
i from
T)) print
'case 15, Solve: true'
else print
'case 15, Solve: FALSE'
drop table
T
go