CODEWITHSUNDEEP
mysqlsql
codlib
codlib

Reputation: 638

MySql rows with NULL values are not retriving in select query

I am having a strange issue with a simple select query and unable to find what is happening. I am using below sql to fetch some records from a customer table. My query is as follows.

SELECT `C`.* FROM `customers` AS `C` WHERE `C`.`typeId`!=9 ORDER BY C.name ASC

My table structure is as follows

|id|name|typeId|
|1 |abc |1     |
|2 |abcd|9     |
|3 |xxxx|NULL  |
|4 |qwe |NULL  |
|5 |xvf |10    |

in the above table, typeId is an integer field and default NULL. Database is MySql

When I am executing the above mentiond SQL, I am getting all other rows except typeId=9 and typeId = NULL. My question is why typeId=NULL is getting omitted from the result? I have googled a lot and unable to find any answer. Am i doing anything wrong here?

Upvotes: 3

Views: 3134

Answers (5)

Sam
Sam

Reputation: 2761

You can use 

SELECT id,
       name,
       COALESE(typeId,0) AS type
FROM customers AS C 
WHERE C.type!=9  
ORDER BY C.name ASC

Upvotes: 1

Vijay
Vijay

Reputation: 8451

try out this...

SELECT `C`.* 
FROM `customers` AS `C` 
WHERE `C`.`typeId`!=9 
   OR `C`.`typeId` IS NULL 
ORDER BY C.name ASC

Upvotes: 4

Nadeem_MK
Nadeem_MK

Reputation: 7699

Because NULL is equivalent to UNKNOWN, and when you compare a value with UNKNOWN, the result will always be false.

If you want to fetch the records containing NULL, you need to re-write your query this way -

where `C`.`typeId` <> 9
OR `C`.`typeId` is null;

Upvotes: 2

Ramesh
Ramesh

Reputation: 1942

Try this

SELECT C.* FROM customers AS C WHERE C.typeId!=9 OR C.typeId IS NULL ORDER BY C.name ASC

Upvotes: 2

Nishant
Nishant

Reputation: 3704

NULL doesn't compare equal to anything. You'll need to accept nulls explicitly: Just add

AND C.typeId IS NOT NULL

in the query also

see the NULL working over here http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Check the below question too :- NULL values in where clause

Upvotes: 1

Related Questions