How to select rows having column value as null?

Question

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?

Answer 1

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; 

Answer 2

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