CODEWITHSUNDEEP
sqlsql-serverpostgresql
afzalex
afzalex

Reputation: 8652

How to select rows having column value as null?

Consider following database table: 

id      |  is_visible
________|_______________
1       |  true
2       |  false
3       |               -- i.e. null

I want to select all the rows having is_visible = null. I tried the following queries:

SELECT * FROM tab WHERE is_visible = null;     -- 0 results
SELECT * FROM tab WHERE is_visible <> true;    -- only fields with false value
SELECT * FROM tab WHERE is_visible <> false;   -- only fields with true

I tried it in postgresql and sql server
How to select rows having is_visible = null?

Upvotes: 30

Views: 45908

Answers (2)

Sundar Singh
Sundar Singh

Reputation: 686

you can't compare 2 null values in sql server as null is unknown or not available. https://technet.microsoft.com/en-us/library/aa196339(v=sql.80).aspx

Use IS NULL operator: 

SELECT * FROM tab WHERE is_visible IS NULL;  -- only field having null value
SELECT * FROM tab WHERE is_visible IS NOT NULL;    -- fields having not null values

Upvotes: 4

chaudharyp
chaudharyp

Reputation: 3614

In both Postgres and SQL server,

SELECT * FROM tab WHERE is_visible is null;

If you want to select the rows for which column values are not null, then use is not null operator:

SELECT * FROM tab WHERE is_visible is not null;

Upvotes: 60

Related Questions