mysqljoinnatural-join

Comparing nulls during join

I read that null cannot be compared with null and the result is always false.

In the below link I am able to compare 2 nulls and the rows are returned. 

CREATE TABLE user (id varchar(50), banstatus varchar(100));
INSERT INTO user (id, banstatus) VALUES ('1', '1');
INSERT INTO user values ('2', 'NULL');

CREATE TABLE banstatus (id varchar(50), texti varchar(100));
INSERT INTO banstatus VALUES('1', 'Banned');
Insert into banstatus values ('NULL' , 'NULL');

select * from user as u
join banstatus as b on u.banstatus=b.id

http://sqlfiddle.com/#!9/33f25/1/0

So what is the correct statement about comparison of nulls ?

Solution

  • The values you have inserted are strings. 'NULL' is a string and is a definite value. To insert NULL you shouldn't use the quotes, for example:

    INSERT INTO table (field1, field2) VALUES ('foo', NULL)

    DEMO

    And you can't compare with NULL, to understand its meaning it's something like undefined. Although, You can test if a value is or is not NULL.