T-SQL not equal WHERE condition excludes records with null values.

Geeks With Blogs

Personal Website / Bio

587 Posts | 965 Comments

My Blog has been MOVED to https://mfreidge.wordpress.com

Michael Freidgeim's OLD Blog My Blog has been MOVED to https://mfreidge.wordpress.com

<< Visual studio conversion wizard opens each time for converted 2008 solution | Home | Call Synchronous Web Services Methods Asynchronously >>

T-SQL not equal WHERE condition excludes records with null values.

Comments (6) | Share

I've had a query which expected to return all records except with specified value in the nullable column.

Select * FROM MyTbl where (MyColumn<>'ValueToExclude')

But the query didn't return any records with null values in the column.

The correct query to include records with null should have explicit is Null condition like the following:

Select * FROM MyTbl where (MyColumn<>'ValueToExclude' or MyColumn is Null)

Update: I found, that it is a well known problem, discussed in many forums, e.g here and here.

Another workarounds are:

where (Coalesce(MyColumn,"")<>'ValueToExclude' ) or

where (IsNull(MyColumn,"")<>'ValueToExclude' )

There is similar article here: http://www.devx.com/vb2themax/Tip/18541 Posted on Tuesday, July 15, 2008 11:45 PM SQL Server | Back to top

Comments on this post: T-SQL not equal WHERE condition excludes records with null values.

# re: T-SQL not equal WHERE condition excludes records with null values.

Ok, but what if the null value column is actually a foreign key coming from a different table. For example, if you have table Student with the columns ID, NAME.... COURSE_ID (which is the FK here). COURSE_ID is null, and you place the condition above

"Select * FROM MyTbl where (MyColumn<>'ValueToExclude' or MyColumn is Null)"

This will include duplicate information in the search result.. It will match each student to each and every course. I don't know if I made sense. But this is a problem I'm facing now, and I'm still looking for a solution.

If anyone knows how to solve this, please email me.

Left by Rawad on Feb 12, 2009 5:33 AM

# re: T-SQL not equal WHERE condition excludes records with null values.

Rawad,
Could you explain your requirements?
Do you want to show all students that have any courses, except cource with COURSEID1? Or something else?

Left by Michael Freidgeim on Feb 12, 2009 8:31 PM

# re: T-SQL not equal WHERE condition excludes records with null values.

Let's take another scenario which might make it simpler...
Father and Son..
1 Father has 0 or more sons
1 Son has 1 and only 1 father

Therefore, in table Son we add the foreign key FATHER_ID as a new field.

Consider this query:
SELECT Son.FNAME, Son.AGE, Father.FNAME, Father.Age
FROM Father, Son
WHERE Son.age < 15 AND Son.FATHER_ID = FATHER.FATHER_ID

If you we able to follow me so far, this query will return the first
names and ages of sons and father, where the son's age is less than
15...
In case the FATHER_ID field in the table "Son" was NULL, this query
won't return any results, because the condition
Son.FATHER_ID = FATHER.FATHER_ID
is not satisfied anymore.

so a solution would be to replace the WHERE statement by this:
WHERE Son.age < 15 AND ( Son.FATHER_ID =
FATHER.FATHER_ID OR Son.FATHER_ID IS NULL )

BUT, this will end up returning all the father's rows for each Son.. u
know what I mean? This happens because we broke the join between the 2
tables...
The true part out of ( Son.FATHER_ID = FATHER.FATHER_ID OR
Son.FATHER_ID IS NULL )
was actually Son.FATHER_ID IS NULL

I'm not sure if u were able to get my point... but I found a
"solution" for this problem

Left by Rawad on Feb 13, 2009 5:37 AM

# re: T-SQL not equal WHERE condition excludes records with null values.

Hi Rawad,

Observe that you´re using a "INNER JOIN" query and this force that every row in the "FROM" table must have a match in the "JOIN" table.

So, If I understand your point I think the better solution is to change the query to a "LEFT JOIN" clause, that every row of the "FROM" table will be returned and only matched rows from the "LEFT JOIN" will be returned.

Left by Alberto HK on May 08, 2009 4:08 AM

# re: T-SQL not equal WHERE condition excludes records with null values.

Thank you so much...

Left by rupesh bari on Feb 24, 2010 11:02 PM

# re: T-SQL not equal WHERE condition excludes records with null values.

I recently ran into the same problem, spotted this post while searching. Mine was a little different, I was comparing something where either side could have been null. Came up with

col1 <> col2 OR (COALESCE(col1, col2) IS NOT NULL AND col1 + col2 IS NULL)

This will allow 'text' <> NULL, the opposite NULL <> 'text' but still have NULL <> NULL working as expected.

Left by David Boyer on Aug 19, 2011 8:56 AM

Your comment:

Title:

Name:

Email: (never displayed) (will show your gravatar)

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Verification:

Code Monkey Projectiles - Index

ASP.Net SQL Server Apple Google SharePoint Windows Visual Studio Team Foundation Server Agile Office Design Patterns Web Azure