2

In this part of query:

. . . WHERE col <> 10

It matches everything except 10 and NULL. While I want to match everything except only 10. So how can I exclude NULL of that?

stack
  • 10,280
  • 19
  • 65
  • 117
  • where col not in(10) should work – Satya May 03 '16 at 05:18
  • where col is null or col <> 10; – Priyanshu May 03 '16 at 05:19
  • 1
    @Satya No, it doesn't work. – stack May 03 '16 at 05:20
  • 1
    Possible duplicate of [Mysql can't select the record in the table](http://stackoverflow.com/questions/36974750/mysql-cant-select-the-record-in-the-table) – e4c5 May 03 '16 at 05:21
  • @Priyanshu Yes it works. But isn't there any better solution? Something like `<=>` which is safe against `null`. Actually your question is fine but it is containing two separated conditions. I like to do that by 1 condition. – stack May 03 '16 at 05:21
  • @stack it should as per the theory. Can you check for the value correctness in col – Satya May 03 '16 at 05:22
  • hi again @stack, hope this answer helps http://stackoverflow.com/questions/36974750/mysql-cant-select-the-record-in-the-table/36974829#36974829 – e4c5 May 03 '16 at 05:22
  • @e4c5 Yes .. it worked perfectly..! +1 for your answer in that question. – stack May 03 '16 at 05:23
  • @stack thanks and glad to have been of help – e4c5 May 03 '16 at 05:23

3 Answers3

5

This you can use for MySQL,

...WHERE IFNULL(col,0) <> 10

If the value of col is NULL then IFNULL(col,0) will convert the value to '0' and perform the comparison. So you will get all the records except only 10.

Abdul Rasheed
  • 6,486
  • 4
  • 32
  • 48
  • That's good .. thank you .. upvote .. Just what's the different `IFNULL()` and `COALESCE()` in this case? – stack May 03 '16 at 06:39
  • Plz refer http://stackoverflow.com/questions/18528468/what-is-the-difference-bewteen-ifnull-and-coalesce-in-mysql – Abdul Rasheed May 03 '16 at 06:42
2

The problem is that the condition col != 10 (in MySQL) means "row where col has a value that is not 10". NULL is not a value so NULL rows aren't matched. If they were, you could have problems with NULLs cascading into other parts of your logic messing things up, since they don't use the same equality logic as values.

As far as I understand, doing it in two conditions (col IS NULL OR col != 10) is the proper way since by MySQL logic you're asking for two separate things. "rows where col has a value that is not 10, or rows where col has no value".

jlaitio
  • 1,878
  • 12
  • 13
2

Try this ....col <> 10 OR col IS NULL

for null values

Jack
  • 510
  • 3
  • 6
  • 22