MySQL comparison with null value
MysqlSqlNullIsnullMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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
.
Solution 4 - Mysql
SELECT *
FROM `table_name`
WHERE IFNULL(`column_name` != 'C', TRUE)
Solution 5 - Mysql
select * from user where application_id='1223333344' and name is null;
Solution 6 - Mysql
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.
Solution 7 - Mysql
I use:
SELECT * from TABLE where NOT(CODE <=> 'C')