null used with logical operator

Question

I have a table with name,age and address.I have totally five rows of data in the table.For some rows the age is left null.I have to display all the data where age is not null.

select * from sample_table where (age !=null);

But no output is displayed and it doesn't give an error also.Please explain this.Thanks.

Answer 1

With NULL you have to use IS or IS NOT. The following query should work:

SELECT * FROM sample_table WHERE (age IS NOT NULL)

Answer 2

The explanation from MySQL

The concept of the NULL value is a common source of confusion for newcomers to SQL, who often think that NULL is the same thing as an empty string ''. This is not the case.

In SQL, the NULL value is never true in comparison to any other value, even NULL. An expression that contains NULL always produces a NULL value unless otherwise indicated in the documentation for the operators and

If you want to search for column values that are NULL, you cannot use an expr = NULL test.

To look for NULL values, you must use the IS NULL test.

Answer 3

You have to use IS NULL or IS NOT NULL.

You can not compare directly against NULL because it is not value (no pedants please!)

NULL on Wikipedia

MySQL, Sybase, SQL Server... it's all the same