CODEWITHSUNDEEP
postgresqlselectnull
Alex
Alex

Reputation: 9720

Select from table where value can be null

I try to select from one table like this:

SELECT * FROM table1 where id=311
         and date BETWEEN '2012-09-01' And '2012-09-09' 
         and col2='a'
         and (col3 ='m' 
              or col3 ='n' 
              or col3=' ' )
ORDER BY date

In the table I have situation where col3 has values 'm', 'n' or null, but this select doesn't return rows where col3 has a null value

Upvotes: 0

Views: 358

Answers (5)

神來之筆
神來之筆

Reputation: 131

There is three-valued logic in database: TRUE, FALSE, NULL(unknown)

col3 ='' is a true condition,

col3 is null is an unknown condition.

They are different.

so you must use

col3 is null

You may reference Wikipedia's "NULL" entry.

Upvotes: 2

wildplasser
wildplasser

Reputation: 44230

Here is a nice trick to avoid the OR list, and to also avoid the OR IS NULL:

SELECT * 
FROM table1
WHERE id=311
 AND zdate BETWEEN '2012-09-01' AND '2012-09-09' 
 AND col2='a'
 AND COALESCE(col3, 'm') IN ('m', 'n' )
ORDER BY zdate
   ;

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

SELECT * 
FROM table1 
where 
    id=311
    and date BETWEEN '2012-09-01' And '2012-09-09' 
    and col2='a'
    and (
        col3 ='m' 
        or col3 ='n' 
        or col3 is null 
    )
ORDER BY date

Upvotes: 1

nos
nos

Reputation: 229058

NULL is not equal to 'm' , 'n' or ' ' . NULL isn't even equal to NULL. Use:

SELECT * FROM table1 where id=311
         and date BETWEEN '2012-09-01' And '2012-09-09' 
         and col2='a'
         and (col3 ='m' 
              or col3 ='n' 
              or col3 IS NULL)
ORDER BY date

Upvotes: 1

wich
wich

Reputation: 17117

NULL is something other than ' ' use col3 is null

Upvotes: 3

Related Questions