MySQL comparison with null value

Question

I have a column called CODE in a MySQL table which can be NULL. Say I have some rows with CODE='C' which I want to ignore in my select result set. I can have either CODE=NULL or CODE!='C' in my result set.

The following query does not return a row with CODE as NULL:

SELECT * from TABLE where CODE!='C'

But this query works as expected and I know it is the right way to do it.

SELECT * from TABLE where CODE IS NULL OR CODE!='C'

My question is why does having only CODE!='C' does not return rows where CODE=NULL? Definitely 'C' is not NULL. We are comparing no value to a character here. Can someone throw some light as why it doesn't work that way?

possible duplicate of [MySQL mystery: Null value is not different from non-null string](http://stackoverflow.com/questions/3404766/mysql-mystery-null-value-is-not-different-from-non-null-string) — Marcus Adams, Mar 07 '12 at 21:33
Does this answer your question? [MySQL mystery: Null value is not different from non-null string](https://stackoverflow.com/questions/3404766/mysql-mystery-null-value-is-not-different-from-non-null-string) — philipxy, Sep 30 '22 at 18:42

score 71 · Accepted Answer · edited Mar 03 '17 at 05:07

71

In MySQL, NULL is considered as a 'missing, unknown value', as opposed to no value. Take a look at this MySQL Reference on NULL.

Any arithmetic comparison with NULL does not return true or false, but returns NULL instead., So, NULL != 'C' returns NULL, as opposed to returning true.

Any arithmetic comparison with 'NULL' will return false. To check this in SQL:

SELECT IF(NULL=123,'true','false')

To check NULL values we need to use IS NULL & IS NOT NULL operator.

edited Mar 03 '17 at 05:07

CrazyCrow

4,125
1
28
39

answered Mar 07 '12 at 20:37

Sam DeHaan

10,246
2
40
48

4

In all relational databases, `null` is an unknown missing value. Nothing mysql-specific about it. – Mike Ryan Mar 07 '12 at 20:49
3

But `NULL` is equivalent to `FALSE` in some situations. So it can be annoying to debug and teh fix can seem rather longwinded and hackish. Consider `UPDATE table SET name=? WHERE id=id AND name<>?` You obviously mean, update if an update would change anything. But if name is null it will simply never update the row. A piece of missing data is not equal to some actual value, so of course it should return true and work. Maybe DBs need a `NULL but TRUE` value type, similar to Perls `0 but true`. – Jonathon Mar 28 '13 at 20:39
1

@JonathonWisnoski No, your example has not changed the nature of `NULL`. Your example provides a case where you want to *treat* `NULL` the same as a non-equal value. In that case, you could use `AND IFNULL(name, 'NOTMYVALUE')<>?` -- that's my understanding, anyway. The `ISNULL` function operates that way in MSSQL, and I believe `IFNULL` works similarly. – Sam DeHaan Apr 01 '13 at 14:01
@Sam DeHaan: Yes, I was not very clear with that. I think a better way to say what I was hinting at is: MySQL has not been true to that description (which I think is a good one) of `NULL`. They have pretty much made the Not null-safe operators Null-safe. In my opinion they should fail and throw an error if they encounter `NULL`, not return 'NULL' which is basically treated as FALSE, as statements get executed on `TRUE` and do not on `FALSE` and `NULL`; In no other language would `if(NULL){}` be considered valid. In reality a 'missing, unknown value' `NULL` is no more FALSE than it is TRUE. – Jonathon Apr 01 '13 at 18:13
1

In my opinion, NULL-safe operators that make sense would be based on probability: `NULL<>?`: An unknown value is almost certainly not going to be equal to some other value (`NULL` or otherwise) so this makes more sense to consider it `TRUE`. It is at least closer to true then it is to false. `NULL=?`: An unknown value is almost certainly NOT equal to some other value, so true makes more sense here than anything else. – Jonathon Apr 01 '13 at 18:18
1

There is nothing different with how MySQL treats `NULL` and `UNKNOWN`. In all SQL implementations (Postgres, SQL-Server, Oracle, DB2) when the outcome of the `WHERE` clause is `UNKNOWN`, the rows are discarded from the results (and so do the rows where the outcome is `FALSE`). – ypercubeᵀᴹ Oct 22 '13 at 22:05
@JonathonWisnoski And you can't say that *"an unknown value is almost certainly NOT equal to some other value."* If a value. unknown, then it could either be equal or not. We are not sure. – ypercubeᵀᴹ Oct 22 '13 at 22:09
C'mon guys, this sucks. One would expect the following statement to be true `select TRUE != NULL`, this is like Javascript's `NaN`. – lucaswxp Dec 03 '20 at 23:04

score 67 · Answer 2 · edited Feb 25 '19 at 04:18

67

Based on my tests and the documentation here: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

You can compare null and get a boolean result using <=>
NOTE: it looks like NOT EQ operator, but it's EQ operator

For example:

select x <=> y; 
or
select @x <=> @y;

This also compares string vs null, string vs string, etc.

edited Feb 25 '19 at 04:18

Vasilii Suricov

871
12
18

answered Oct 22 '13 at 21:53

Alan Fullmer

1,086
9
8

4

As @Alan_Fullmer mentioned "<=">" is an EQual operator . So if you want to check for NEQ (<>) then simply surround your statement with not. e.g. NOT(X <=> y). – JSS Mar 13 '19 at 13:47

cornuz · Answer 3 · 2019-09-02T23:06:56.503

20

In SQL, the NULL value is a special value, not comparable with any other one. The result of a direct comparison with a NULL is always NULL, although (unfortunately) you may find FALSE in some implementation.

To test a null value you should use IS NULL and IS NOT NULL.

edited Sep 02 '19 at 23:06

answered Mar 07 '12 at 20:39

cornuz

2,678
18
35

Often referred to as Ternary Logic. – sceaj Mar 07 '12 at 21:26
The comparison of `NULL` to any value, even to itself is always `NULL` (or `UNKNOWN` in some implementations) but never `FALSE`. Where have you seen that behaviour? – ypercubeᵀᴹ Oct 22 '13 at 22:12
@ypercube, I completely agree with you, but unfortunately such cases do exist. Look for example at the accepted answer of: http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server . and notice the "not true" – cornuz Oct 27 '14 at 08:20
@comuz that answer is only talking about a specific system (SQL-Server) and a specific setting (ansi nulls off). Which makes SQL-Server treats nulls differently than the SQL standard dictates. If you mean that, ok. Otherwise, in SQL, `Whatever = NULL` results in `UNKNWON`. – ypercubeᵀᴹ Oct 27 '14 at 09:24
Indeed, what I meant is that some implementations / settings can result in the comparison being FALSE/TRUE. – cornuz Oct 27 '14 at 10:13

score 8 · Answer 4 · answered Oct 01 '12 at 07:38

8

SELECT * 
FROM `table_name` 
WHERE IFNULL(`column_name` != 'C', TRUE)

answered Oct 01 '12 at 07:38

Vasil Nikolov

1,112
10
17

Vipin Pandey · Answer 5 · 2020-06-20T15:33:50.710

2

select * from user where application_id='1223333344' and name is null;

edited Jun 20 '20 at 15:33

answered Jan 17 '17 at 09:17

Vipin Pandey

659
8
17

score 2 · Answer 6 · answered Jun 08 '20 at 11:13

The specified problem can also appear in joins and the above answers aren't particularly helpful. The way I prefer to do it is by coalescing to otherwise impossible value. For example, this

   select foo from bar
     inner join baz on bar.x = baz.y

won't work if bar.x and baz.y are both nulls (join won't bring results). The workaround is to use e.g.

   select foo from bar
     inner join baz on coalesce(bar.x, -1) = coalesce(baz.y, -1)

where -1 is "impossible" value meaning it can never appear in the data set.

score 2 · Answer 7 · answered Jan 17 '22 at 01:54

2

I use:

SELECT * from TABLE where NOT(CODE <=> 'C')

answered Jan 17 '22 at 01:54

Robert Máslo

208
2
5

MySQL comparison with null value

7 Answers7

Linked

Related