Posted by Malcolm Dew-Jones on 06/03/06 01:00

meltedown (groups2@reenie.org) wrote:
: This is probably a stupid question but I don't get it.
: I have a table called sites

: 2 rows have a name='siteisdown'
: 1 row has a value of 1 and the other has a value of '' (nothing)

: This works fine, it returns 1 row with a value of 1, along with all the
: other columns.
: "SELECT * from sites where name='siteisdown' AND value =".TRUE);

?

: This returns nothing:
: "SELECT * from sites where name='siteisdown' AND value ='' ";

Try the syntax

SELECT * from sites where name='siteisdown' AND value is NULL;


The database is probably one that uses '' as a shorthand for the NULL
value. NULL is a special value that represents having no value or a value
which is unknown. There aren't any values that can ever be equal "no
value", and so value='' selects nothing.

As an aside, you'll note that "NULL = NULL" will also return no rows.
That is because NULL also represents an "unknown" value, and it isn't
possible to say that two unknown values are the same as each other (since
you don't know what values they are).

[Back to original message]