Re: [SQL] how to tell the difference between empty field and null field

Far be it from me to defend Microsoft, but not all sloppiness is down
to Microsoft. All Microsoft databases support IS NULL, and indeed
advocate it: from the Access help file...

Searching for Null values or zero-length strings
----------------------------------------------------------------
If you're using a query to search for Null values or zero-length
strings, type Is Null into the Criteria cell to search for Null
values, or type two double quotation marks (" ") into the Criteria
cell to search for zero-length strings (don't type a space between the
quotation marks).

Yours,
Moray
----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Howansky <alex@wankwood.com>
Cc: <pgsql-sql@postgreSQL.org>
Sent: Monday, December 13, 1999 5:14 AM
Subject: Re: [SQL] how to tell the difference between empty field and
null field


> Alex Howansky <alex@wankwood.com> writes:
> > select * from users where domain = '' or domain = null;
>
> OK, that'll work, but if you'll pardon a nitpick: "= NULL" is not
> standard, it is Microsoft brain damage.  "IS NULL" is standard.
>
> > Here's my question: if I have a zillion records in this table, and
> > it's indexed by user+domain, how can I run this query without
losing
> > the benefit of the index?
>
> An index on (user, domain) is perfectly useless for the above query,
> because the user field isn't mentioned anywhere in the query.  An
index
> on domain alone could be used, though, and should be pretty
effective.
> (We do have some performance problems if you get into dozens of OR
> terms, but for just a couple, no sweat.)
>
> As a rule of thumb, multi-column indexes are quite inflexible, and
> you will not find that they are worth their cost of upkeep unless
> you know that you have a specific kind of query you use a *lot* that
> can exploit the multi-column index.  Not only that, but that the
> heavily used query is significantly faster than it'd be if it only
> had an index on the first column named in the multi-column index.
> In most scenarios, the first column gives you most of the gain and
> any extra columns are marginal.
>
> In short, unless you've done careful analysis and testing, you
should
> not make an index on (user, domain) but two indexes on user and
domain
> separately.  The latter setup will be a lot more flexible in terms
of
> being reasonably quick for a variety of queries.
>
> regards, tom lane
>
> ************
>
>