Re: selecting a NULL value?????

On 24 Aug 1998 14:00:29 PDT, lkolinek_at_concentric.net (larryk) wrote:

>Here is a select statement:
>
>SELECT *
>FROM this_table
>WHERE (char_field = ''
>OR char_field = null )
>
>
>this_table.char_field contains nothing. Why does this SQL statement
>not work?

Larry,

You should use "OR char_field IS null". The IS NULL operator returns true if the value in question is a null, otherwise it returns false.

The reason that "char_field = null" does _not_ work is rooted in the fact that a null value is considered to be an unknown value. If char_field is null, then it is unknown. And if the value is unknown, then it is impossible to know whether or not it equals anything else.

Three valued logic can get a bit weird sometimes. The following SELECT for example, will not neccessarily return all rows from a table:

	SELECT * FROM TABLE
	WHERE X = 2
	  AND X <> 2;

You would think the above select would return every record in the table, but any rows where X is null won't be returned.

regards,

Jonathan Gennick Received on Tue Aug 25 1998 - 21:27:04 CDT