select NULL and false but not true in sql

Question

i an new in vb.ner sql and what i am asking may be silly question. I have a table in sql server 2005 and a column name activated. this column contain NULL, true or false.

I want to select only NULL or false values. How can i do this?

Answer 1

You should be able to do a coalesce on the field to get it to false if it is null. A coalesce statement checks to see if the first parameter is null, if it is it returns the value in the second parameter. There would be two solutions:

SELECT *
FROM MyTable
WHERE COALESCE(activated,'false') <> 'true'`

--OR--

SELECT *
FROM MyTable
WHERE activated = 'false' or activated is null

Answer 2

With SQL's 3-valued logic, comparisons for NULL need to be done using the IS NULL operator... the check for false can be done with =:

SELECT YourColumn
FROM YourTable
WHERE 
  YourColumn IS NULL 
  OR YourColumn = 0

Answer 3

SELECT * FROM MyTable WHERE isnull(activated, 0) = 0

Answer 4

For checking NULL you have to use the keyword "IS NULL" and for false =0 like below

SELECT * FROM MyTable WHERE activated = 0 OR activated is NULL

Answer 5

Sometimes you have to see it to believe...

PRINT '0 = 0'       ; IF 0 = 0        PRINT 'true' ELSE PRINT 'false';PRINT '';
PRINT '0 = 1'       ; IF 0 = 1        PRINT 'true' ELSE PRINT 'false';PRINT '';
PRINT '0 = NULL'    ; IF 0 = NULL     PRINT 'true' ELSE PRINT 'false';PRINT '';
PRINT '1 = NULL'    ; IF 1 = NULL     PRINT 'true' ELSE PRINT 'false';PRINT '';
PRINT 'NULL = NULL' ; IF NULL = NULL  PRINT 'true' ELSE PRINT 'false';PRINT '';
PRINT 'NULL IS NULL'; IF NULL IS NULL PRINT 'true' ELSE PRINT 'false';PRINT '';

Answer 6

The SQL way is to use IS TRUE or similar:

SELECT * FROM MyTable WHERE activated = 0 IS NOT FALSE