CODEWITHSUNDEEP
sql-server-2005
chaitanya
chaitanya

Reputation: 1

Problem With <> while comparing null values in SQL Server 2005

I have four columns name, Level1, Level2, Level3 and by default null values, I want to retrieve all the rows that do not have Level1, Level 2, Level 3 completed.

This is my my query but it is not fetching any values. Please help me

select name from table
where Level1 <>'Completed' and Level2 <> 'Completed and Level3 <> 'Completed'

Or 

select name from table
where Level1 <>'Completed' or Level2 <> 'Completed or Level3 <> 'Completed' .

But it is not fetching any rows. Is there a problem with the <> operator or the NULL values?

Upvotes: 0

Views: 1313

Answers (3)

marc_s
marc_s

Reputation: 755207

Since you need to treat NULL "non-values" separately, try something like this:

SELECT name 
FROM dbo.table
WHERE ISNULL(Level1, 'null') <> 'Completed' 
  AND ISNULL(Level2, 'null') <> 'Completed'
  AND ISNULL(Level3, 'null') <> 'Completed'

If one of the columns is NULL, then that NULL will be replaced with whatever value you pass in as the second arugment to ISNULL() - and since I'm replacing a NULL with the 'null' string - it's not equal to 'Completed' and will be selected.

Upvotes: 1

codingbadger
codingbadger

Reputation: 44032

A column with a NULL means that the column value is unknown or not available. A NULL does not equal 0 or zero length strings.

In order to filter rows based on a NULL value you have to use IS NULL or IS NOT NULL

To find rows where any one of your columns are NULL you could use the following query:

Select *
From YourTable
Where Level1 Is Null Or Level2 Is Null Or Level3 Is Null

See this MSDN Article for a full explanation of NULL Comparisions in MS SQL Server

Upvotes: 0

StKiller
StKiller

Reputation: 7951

For null values you should use value is not null or value is null

Upvotes: 3

Related Questions