Why does this simple MySQL query not return the ro

Question

I have a row in the table users with the username test. For some reason, though, this query returns an empty result set.

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` != null;

However, if I remove the `id` != null segment, the query returns the result id = 1.

But 1 != NULL. How is this happening?

The id field is non-nullable and is auto-increment.

Thanks!

Answer 1

By database definition in general ,Null is nothing and cannot be equated or compared with any other value. Hence ID=NUll or ID!=null wouldn't work.

Answer 2

Your method of checking for NULL is probably the issue. In MySQL, try the following:

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` IS NOT NULL;

To check for NULL and an empty string, you can use:

SELECT `id` 
FROM `users` 
WHERE `username` = "test" 
AND (`id` IS NOT NULL OR `id` != "");

Answer 3

Try:

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` != '';

OR

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` iS NOT NULL;

Answer 4

The query doesn't return a row because the predicate " id != NULL " will never return TRUE.

Th reason for this is that boolean logic in SQL is three valued. A boolean can have values of TRUE, FALSE or NULL.

And an inequality comparison will return NULL whenever one (or both) of the values being compared is NULL.

The SQL standard means to compare to a NULL is to use id IS NULL or id IS NOT NULL. MySQL also adds a convenient null-safe comparison operator which will return TRUE or FALSE:

col <=> NULL. Or, in your case NOT (col <=> NULL)

Answer 5

try using IS NOT NULL

SELECT `id` FROM `users` WHERE `username` = "test" AND `id` IS NOT NULL

Have a look at the difference

SQL Fiddle DEMO