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
>
> ************
>
>