<< 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.

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
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati
posted @ Tuesday, July 15, 2008 11:45 PM
Print

Comments on this entry:

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

Left by Rawad at 2/12/2009 5:33 AM
Gravatar
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.

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

Left by Michael Freidgeim at 2/12/2009 8:31 PM
Gravatar
Rawad,
Could you explain your requirements?
Do you want to show all students that have any courses, except cource with COURSEID1? Or something else?

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

Left by Rawad at 2/13/2009 5:37 AM
Gravatar
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

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

Left by Alberto HK at 5/8/2009 4:08 AM
Gravatar
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.

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

Left by rupesh bari at 2/24/2010 11:02 PM
Gravatar
Thank you so much...

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

Left by David Boyer at 8/19/2011 8:56 AM
Gravatar
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.

Your comment:



(not displayed)


 
 
 
 
 

Live Comment Preview:

 
«February»
SunMonTueWedThuFriSat
2930311234
567891011
12131415161718
19202122232425
26272829123
45678910

About
Article Categories
Archives
Post Categories
Links
Syndication:

RSS ATOM