Using null value in SQL

Question

I have a database table: Employee with fields id, name, sex, age.

I run the following queries, and they gave different results. So what's the difference between them?

SELECT name, sex
FROM Employee
WHERE age = null

or

SELECT name, sex
FROM Employee
WHERE age is null

Any comparison to `NULL` returns `NULL` (which is treated as false) except for `IS NOT NULL` and `IS NULL`. — Gordon Linoff, Jun 28 '15 at 20:53

score 1 · Answer 1 · answered Jun 28 '15 at 21:14

In SQL there's three-valued-logic, TRUE/FALSE/UNKNOWN. Any comparison to NULL results in UNKNOWN, both following queries will always return an empty result set regardless if there are NULLs in age:

select name,sex
from employee 
where age=null;

select name,sex
from employee 
where age<>null;

To get the correct result you need either where age IS NULL; for employees with unknown age or where age IS NOT NULL; for all employees with known age.

Using null value in SQL

1 Answers1