CODEWITHSUNDEEP
sqlmariadb
Melon
Melon

Reputation: 614

MariaDB cannot evaluate SELECT 1 WHERE NULL = NULL;

I'm currently in process of learning SQL. I have an example from a book that I'm trying to understand, but MariaDB cannot evaluate it:

SELECT 1
WHERE NULL = NULL
UNION
SELECT 0
WHERE NULL <> NULL;

This however gives the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE NULL = NULL
UNION
SELECT 0
WHERE NULL <> NULL;' at line 2

I have tried:

SELECT 1;

which works. However this doesn't:

SELECT 1 WHERE NULL = NULL;

I have also tried adding parantheses, but that didn't help much. What am I doing wrong here? This example was to show that comparing NULL with any other value gives "Unknown".

Upvotes: 0

Views: 595

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31812

AFAIK you can not use WHERE without FROM. SELECT 1 WHERE 1 = 1 will also not work.

To fix your example you could select from a dummy table:

SELECT 1
FROM (SELECT 'dummy_value') dummy_table
WHERE NULL = NULL
UNION
SELECT 0
FROM (SELECT 'dummy_value') dummy_table
WHERE NULL <> NULL

But a better way to see how MySQL behaves could be

SELECT 
    NULL = NULL,
    NULL <> NULL,
    NULL IS NULL,
    NULL IS NOT NULL,
    1 = NULL,
    1 <> NULL,
    1 IS NULL,
    1 IS NOT NULL

Upvotes: 2

Dave Gray
Dave Gray

Reputation: 723

That's the expected behavior, according to the MariaDB docs on NULLs:

NULL values cannot be used with most comparison operators. For example, =, >, >=, <=, <, or != cannot be used, as any comparison with a NULL always returns a NULL value, never true (1) or false (0).

Notice that the example on that docs page has the NULL = NULL in the column list, not in the where clause, since the column list will return the result of the comparison, and the where clause requires a boolean expression.

Upvotes: 1

Related Questions