CODEWITHSUNDEEP
mysqlnull
Warrior
Warrior

Reputation: 39374

null used with logical operator

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.

Upvotes: 3

Views: 2513

Answers (3)

gbn
gbn

Reputation: 432210

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

Upvotes: 1

jerebear
jerebear

Reputation: 6655

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.

Upvotes: 2

Ali
Ali

Reputation: 267059

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

SELECT * FROM sample_table WHERE (age IS NOT NULL)

Upvotes: 4

Related Questions