Previous Topic

Next Topic

Inhoudsopgave

Book Index

Cases with X <> Y

/* 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