why is that when I use the <> in sql it is not getting the null value

Question

Example I have the following records on column_name "color": blue, red, green, null... when I do a select query .. select * from table_name where color <> red... The sql should return all records except those that have color red, right? Why is it not showing those value that have null as color? Thank you!

Answer 1

In Sql server to compare any value with null you have to use IS NULL or IS NOT NULL

So your query will be

Select * from table_name where color IS NOT NULL

Answer 2

Take a look at SET ANSI_NULLS for an understanding of why this happens.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. [...] a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.

Update: sorry this is for SQL Server and your question is Oracle, however I'll leave this here as it's probably still relevant.

Answer 3

In general, any comparison with NULL returns false. If you want to also get NULL rows, you'll have to explicitly ask for them:

color <> 'red' OR color IS NULL

Answer 4

Use where isnull(color,'') <> 'red'

Null isn't a value. Same applies for avg, sum, etc.