Using null value in SQL [duplicate]

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
– Gordon Linoff, Commented Jun 28, 2015 at 20:53

dnoeth · Accepted Answer · 2015-06-28 21:14:50Z

1

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.

answered Jun 28, 2015 at 21:14

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using null value in SQL [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related