Jay A. Kreibich, Thank you for the discussion about the SQLITE difference
between DISTINCT and UNIQUE.
------------------------------
Message: 14
Date: Wed, 12 Dec 2012 10:15:34 -0600
From: "Jay A. Kreibich" <j...@kreibi.ch>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] SQLite :Is there any subtle distinction between
UNIQUE and DISTINCT?
Message-ID: <20121212161534.gb68...@dfjk.org>
Content-Type: text/plain; charset=us-ascii
On Wed, Dec 12, 2012 at 06:26:54AM -0800, Frank Chang scratched on the wall:
> Hello, In the latest version of SQLite, is there any subtle distinction
> between UNIQUE and DISTINCT? If there exists a subtle distinction between
> UNQUE and DISTINCT in Sqlite, what is the appropriate Insert or replace
> syntax, CREATE INDEX usage, and the SELECT statements? Thank you.
Yes, there is a difference in how they handle NULLs. UNIQUE tests on
"=" (equals), while DISTINCT tests on "IS". Basically UNIQUE
considers any two NULLs to be unique, while DISTINCT considers any two
NULLs to be identical.
In short, if you have a table with UNIQUE constraints (but without
a NOT NULL constraint) there may still a valid reason to use SELECT
DISTINCT.
As for syntax and so forth, the terms are not interchangeable. The
docs on the sqlite.org website should explain when you can use one
term or the other.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
