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

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.

Your comment:



 (will not be displayed)


 
 
 
 
 

Live Comment Preview:

 
«November»
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345

About
Article Categories
Archives
Post Categories
Links
Syndication:

RSS ATOM