CODEWITHSUNDEEP
sqlsql-server-2012
yogi
yogi

Reputation: 19601

<> operator in SQL

I have a table like this

ID   Name    IsDeleted
1    Yogesh  Null
2    Goldy   1

Now when I run this query

select  * 
from tableName 
where IsDeleted <> 1

I should get ID 1 record, But I am not getting it,

But when I run this 

select  * 
from tableName 
where IsDeleted is null

I get ID 1 record,

Why am I facing this behavior ?? Isn't NULL <> 1 is a true statement in SQL ??

IsDeleted is a bit type field with Allow Null true

Upvotes: 4

Views: 2631

Answers (6)

PaulG
PaulG

Reputation: 592

Normal practice would dictate that if you had a column that essentially was a true false, yes no type of field then you should use a bit field with the default value set to 0.

So in your case above you could just run this:

select  * 
from tableName 
where IsDeleted = 0

But in answer to your above question, if the Null is a true NULL value in the table then this will work for you:

select  * 
from tableName 
where IsDeleted is null

or

select  * 
from tableName 
where isnull(IsDeleted,0) = 0

to get record 1 and

select  * 
from tableName 
where IsDeleted is not null

to get record 2

Good luck

Paul.

Upvotes: 1

Lucero
Lucero

Reputation: 60190

Learn about NULL - a comparison with NULL (in standard SQL) yields UNKNOWN, which is not true nor false (and the reason why your expectation is not met).

Try this:

PRINT CASE
    WHEN 1 = NULL THEN '1 = NULL'
    WHEN 1 <> NULL THEN '1 <> NULL'
    ELSE '1 is neither = NULL nor <> NULL'
END

You can either first make sure that you don't have a NULL value (for instance by using the ISNULL or COALESCE functions), or use a condition with the operator IS NULL or IS NOT NULL.

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

select * from table 
where COALESCE(IsDeleted, 0) <> 1 
-- or ISNULL instead of COALESCE. 
--ISNULL seems to be better in subqueries, but it's not ANSI SQL.

or

select * from table 
where IsDeleted <> 1 or IsDeleted IS NULL

Upvotes: 4

di3
di3

Reputation: 594

you compare different types. in this case its an other type (unknown) and not comparable

use the or statement to compare each type seperate

WHERE IsDeleted <> 1 OR IsDeleted is null

Upvotes: 1

Joe G Joseph
Joe G Joseph

Reputation: 24046

select  * 
from tableName 
where isnull(IsDeleted,0) <> 1

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Comparing something with null will always result in unknown. That is why you need to use the is operator to compare null or use functions like COALESCE or isnull to replace null

Upvotes: 1

Related Questions