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
