From: Michael Dykman
Date: December 28 2009 7:35pm
Subject: Re: Is anything ever equal to NULL?
List-Archive: http://lists.mysql.com/mysql/219956
Message-Id: <814b9a820912281135p6048d023y42034d2c5db72c43@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

No, nothing will ever equal null. In strict relational theory, which I
don't know well enough to begin expounding on here, null does not even
equal another null.  That's why SQL provides IS NULL and IS NOT NULL
as explicit cases.

 - michael dykman


On Mon, Dec 28, 2009 at 2:32 PM, D. Dante Lorenso <dante@stripped> wrote=
:
>
> Will anything ever be equal to NULL in a SELECT query?
>
> =A0SELECT *
> =A0FROM sometable
> =A0WHERE somecolumn =3D NULL;
>
> I have a real-life query like this:
>
> =A0SELECT *
> =A0FROM sometable
> =A0WHERE somecolumn =3D NULL OR somecolumn =3D 'abc';
>
> The 'sometable' contains about 40 million records and in this query, it
> appears that the where clause is doing a sequential scan of the table to
> =A0find a condition where 'somecolumn' =3D NULL. =A0Shouldn't the query p=
arser be
> smart enough to rewrite the above query like this:
>
> =A0SELECT *
> =A0FROM sometable
> =A0WHERE FALSE OR somecolumn =3D 'abc';
>
> And therefor use the index I have on 'somecolumn'? =A0When I manually rew=
rite
> the query, I get the performance I expect but when I leave it as it was,
> it's 100 times slower.
>
> What's so special about NULL?
>
> -- Dante
>
> ----------
> D. Dante Lorenso
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
.com
>
>



--=20
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.