In my database table I am having one boolean column, which have some rows with False, True and Null.
These are the cases I have tried:
Case 1:
select * from table_name where
boolean_column is null;
works well. Give the result with all rows having null value for that column.
Case 2:
select *from table_name where boolean_column = False;
Works well. Gives result with all the rows having False value for that column.
Case 3:
This is requirement which does not works. I want all the rows having values False and Null.
I have tried these.
i) select *from table_name where boolean_column is False or Null;
Which only gives the result for False it does not shows null records.
ii) select *from table_name where boolean_column is Null or False;
Which only gives the result for null it does not shows records with False value.
iii) select *from table_name where boolean_column is Null or boolean_column = False;
This simply displays all the rows, it does not apply any condition at all.
What query can solve this problem?
Use the IS NOT TRUE operator:
SELECT * FROM table_name WHERE boolean_column IS NOT TRUE;
This will match values which are either false or NULL. Another option would be to use a UNION of the two queries which you know do work:
SELECT * FROM table_name WHERE boolean_column IS NULL
UNION
SELECT * FROM table_name WHERE boolean_column = FALSE
You could also try using COALESCE:
SELECT * FROM table_name WHERE COALESCE(boolean_column, FALSE) = FALSE
This third query will replace all NULL values with FALSE and then compare against FALSE in the WHERE condition.