Tony Stark Tony Stark - 2 months ago 6
SQL Question

why can't i use "where R.ApartmentID = null" to retrieve all non matching rows from the left table

I only have a month of learning experience on sql server and I was just wondering why the first query before produces the right results (i.e. join two table and only select rows from left table that does NOT have a matching row), whereas the second query returns an empty query.

First

select R.Name, A.Name
from tblResident as R
left join tblApartment as A
on R.ApartmentID = A.ID
where R.ApartmentID is null


Second

select R.Name, A.Name
from tblResident as R
left join tblApartment as A
on R.ApartmentID = A.ID
where R.ApartmentID = null


Table structure

enter image description here

Answer

for MySQL:

As per MySQL Documentation on Working with NULL Values:

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL. Because the result of any arithmetic comparison with NULL is also NULL, you cannot obtain any meaningful results from such comparisons

And hence are the results you experienced.

Examples:

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL,
     > 1 is null, null is null;
+----------+-----------+----------+----------+-----------+--------------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | 1 is null | null is null |
+----------+-----------+----------+----------+-----------+--------------+
|     NULL |      NULL |     NULL |     NULL |         0 |            1 |
+----------+-----------+----------+----------+-----------+--------------+

I think the same is applicable for all other DB platforms.