Re: selecting all records where a column is null

On Thu, Jun 20, 2002 at 11:46:43AM -0700, Elmshauser, Erik wrote:
> Hi, I am trying to select every record from a table where a specific
> column does not have a value in it yet.

What you are looking for is NULL, as you note.  Your problem is this:

> select * from table where field = NULL;

To make an oversimplification, SQL uses a three-value logic: T,F, and
NULL.  NULL is for unknown cases.  So

    NULL = NULL

is false, because the left side is unknown, and the right side
is unknown also.  (It might be that if you knew the two values, they
would not be equivalent.)  What you want is

SELECT * FROM table WHERE field IS NULL;

In 7.2.1, there is a switch that allows you to turn on "= NULL"
conversion, in order to support some old behaviour (I think to
support Access, but I forget).  But unless you need it to support
some external program, don't use it.  Use the IS NULL locution
instead, because it'll prevent you from getting snagged by this on
other systems.

A


--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110