CODEWITHSUNDEEP
sql-server
martijn
martijn

Reputation: 1469

Is there a ms sql setting that allows value != null to be true

in MS SQL I have come accross a query like below:

select Wubba, Dubba
from Dub as dub
where Dubba != 10

When Dubba is null, the select returns zero rows. Since im looking for a bug, I was wondering if there a setting in MS SQL that would allow this query to return values.

Upvotes: 4

Views: 233

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

There is no setting which provides the behaviour you are looking for..you can try below

Dubba != 10 or dubba is null

Using ISNULL(somecol,val) is not Sargable

there is a setting called SET ANSI_NULLS ,but this won't work in comparisons

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name

When SET ANSI_NULLS is OFF, A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name

When SET ANSI_NULLS is ON Null <> Null
When SET ANSI_NULLS is OFF Null = Null

But you are asking to include nulls ,if the column has NULL values,only way to get nulls is, to specify

Further reading from Itzik Ben-Gan:

Don’t Avoid the UNKNOWN
Predicate-Based Query Filters

Upvotes: 3

Roman Marusyk
Roman Marusyk

Reputation: 24589

Seems there is no setting, so you can use:

select Wubba, Dubba
from Dub as dub
where ISNULL(Dubba, 0) != 10

Upvotes: 2

Neo
Neo

Reputation: 3399

select Wubba, Dubba
from Dub as dub
where Dubba != 10 Or Dubba Is Null

As there is no server setting

Upvotes: 0

Related Questions