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

Question

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

Possible duplicate of [what is "=null" and " IS NULL"](http://stackoverflow.com/questions/2749044/what-is-null-and-is-null) — diiN__________, Sep 28 '16 at 07:07
this may sounds ignorant but does using mysql or sql server makes a difference in this incidence? — Thor, Sep 28 '16 at 07:07
No, it's the same for every DBMS, some of them fail with an error for the 2nd condition. But why do you tag `mysql` when you use SQL Server? — dnoeth, Sep 28 '16 at 07:15
Possible duplicate of [SQL is null and = null](http://stackoverflow.com/questions/9581745/sql-is-null-and-null) — Drew, Sep 28 '16 at 12:59

Ravinder Reddy · Accepted Answer · 2016-09-28T08:40:22.020

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, null = null;
+----------+-----------+----------+----------+-----------+--------------+-------------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | 1 is null | null is null | null = null |
+----------+-----------+----------+----------+-----------+--------------+-------------+
|     NULL |      NULL |     NULL |     NULL |         0 |            1 |        NULL |
+----------+-----------+----------+----------+-----------+--------------+-------------+

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

score 1 · Answer 2 · answered Sep 28 '16 at 07:21

Hi Tony,
Good question
You are checking a null column. Basically null is nothing or empty in a simple english. You cannot use operators to select null values. Operators can be used only to match the column which has a value and not null. In this case, is null fucntion is to be used

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

2 Answers2