CODEWITHSUNDEEP
sql
dineshdileep
dineshdileep

Reputation: 805

Selecting rows which are not null in sql

Why doesn't the following code work in SQL

SELECT * 
FROM DATA
WHERE VALUE != NULL;

Upvotes: 4

Views: 15971

Answers (6)

Rahul Parit
Rahul Parit

Reputation: 331

Null does not contain any value. Null is neither zero nor any value. So we can't compare it using comparison operators. Instead of using '=', you should use 'IS' keyword.

SELECT * FROM DATA WHERE VALUE IS NOT NULL;

Upvotes: 0

Eli.Brighton
Eli.Brighton

Reputation: 86

The code will not work in SQL because it is not possible to test for NULL values with the following operators =, <, or <>. It is not possible to compare NULL and 0 as they are not equivalent.

You can test for NULL values using the IS NULL and IS NOT NULL operators instead.

SELECT * 
FROM DATA
WHERE VALUE IS NOT NULL

http://www.w3schools.com/sql/sql_null_values.asp

Upvotes: 1

Molloch
Molloch

Reputation: 2381

Because a NULL value indicates an absence of data, it doesn't make sense to compare something with a value to it with an equals operator.

You can make your SELECT statement work by setting ANSI_NULLS OFF

SET ANSI_NULLS OFF

before running the statement. You can get further information here:

https://msdn.microsoft.com/en-us/library/ms188048.aspx

Upvotes: 0

Rojalin Sahoo
Rojalin Sahoo

Reputation: 1025

The condition you written is not in proper format. If you want to select not null values from your table then you can use the following command 

select *from table name where column name IS NOT NULL

Upvotes: 2

Dhaval
Dhaval

Reputation: 2379

we can not Compare null value with = We Special operator to compare null value in sql IS OPERATOR 

SELECT * 
FROM DATA
WHERE VALUE is not NULL;

Null is not any Value.Sql consider Null as Unknown/absence of data.

Upvotes: 6

John Hodge
John Hodge

Reputation: 1715

If should work if you replace "!=" with "IS NOT".

Upvotes: 1

Related Questions