Incorrect no. of rows fetched in SQL

Question

Please consider a table named Employee with following two columns:

EmpType has two valid values either ES or NULL.

When I fire below query I get 100 rows

select * from Employee;

Below query gives me 60 rows:

select * from Employee where EmpType = 'ES'

While select * from Employee where EmpType <> 'ES' gives me 0 rows.

Why so? I should get 40 rows where EmpType is not ES.

I am working on Oracle Sql DB.

try using `select * from Employee where EmpType is null` to get nulls `emptype` and `select * from Employee where EmpType is not null` to get `emptype`= `ES` — Aramillo, Nov 24 '14 at 16:51
Related: [Not equal <> != operator on NULL](http://stackoverflow.com/questions/5658457/not-equal-operator-on-null) — Jonathan Lonowski, Nov 24 '14 at 16:53

crthompson · Accepted Answer · 2014-11-24T17:01:25.170

2

Null does not equal anything, not even another null. So to say not equal to ES, null is not not equal to ES. If you get my meaning.

So to get the values you want you need is null.

select * from Employee where EmpType is null

Check out this article on handling null values

EDIT: As noted by @Allan, in Oracle an empty string is equivalent to null.

edited Nov 24 '14 at 17:01

answered Nov 24 '14 at 16:45

crthompson

In Oracle, an empty string is equivalent to null, so the last SQL statement will not work. – Allan Nov 24 '14 at 17:00

score 0 · Answer 2 · answered Nov 25 '14 at 08:26

0

the "<>" used with number not with nvarchar

so you should use instead :

select * from Employee where EmpType + '' like 'null'

select * from Employee where EmpType is null

answered Nov 25 '14 at 08:26

"*the "<>" used with number not with nvarchar*" is plain wrong. The not equal operator `<>` can very well be used for varchar columns. The operator `+` on the other side is *only* used for numbers in SQL (to concatenate strings you have to use `||` in standard and Oracle). And the expression `EmpType + '' like 'null'` will not work at all – Nov 25 '14 at 08:31

2 Answers2