Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!elnk-pas-nf1!newsfeed.earthlink.net!stamper.news.pas.earthlink.net!stamper.news.atl.earthlink.net!newsread1.news.atl.earthlink.net.POSTED!3ce9497d!not-for-mail Reply-To: "David Cressey" From: "David Cressey" Newsgroups: comp.databases.theory References: <2KXgf.1686$ea6.1270@news-server.bigpond.net.au> <1132794767.327020.194730@g49g2000cwa.googlegroups.com> <1132798136.903908.163790@f14g2000cwb.googlegroups.com> <1132798608.160942.270970@o13g2000cwo.googlegroups.com> <1132867508.797608.286670@o13g2000cwo.googlegroups.com> <1132926804.625596.34020@g47g2000cwa.googlegroups.com> <1133040978.145734.282720@z14g2000cwz.googlegroups.com> <1133114041.839154.239970@g47g2000cwa.googlegroups.com> <1133191499.952147.182080@z14g2000cwz.googlegroups.com> Subject: Re: So what's null then if it's not nothing? Lines: 51 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 Message-ID: Date: Tue, 29 Nov 2005 14:00:42 GMT NNTP-Posting-Host: 4.157.86.191 X-Complaints-To: abuse@earthlink.net X-Trace: newsread1.news.atl.earthlink.net 1133272842 4.157.86.191 (Tue, 29 Nov 2005 06:00:42 PST) NNTP-Posting-Date: Tue, 29 Nov 2005 06:00:42 PST Organization: EarthLink Inc. -- http://www.EarthLink.net Xref: dp-news.maxwell.syr.edu comp.databases.theory:34739 "Jon Heggland" wrote in message news:MPG.1df62f87b42a94ca989713@news.ntnu.no... > In article <1133191499.952147.182080@z14g2000cwz.googlegroups.com>, > boston103@hotmail.com says... > > > > Re. projection. The projection definition uses the notion of set > > membership so that the issue of duplicates and therefore their > > elimination via comparison does not arise at the logical level either > > with respect to nulls or non-nulls. > > Sophistry. A SELECT DISTINCT (it is really too kind to call this SQL > construct "projection") eliminates duplicate NULLs, just like it > eliminates duplicate values. To handwave this by saying "well, they're > not *distinct*, but that doesn't mean they're *equal*" is just a bad > excuse. > It comes as a surprise to me to learn that the SQL standard does not specify clearing NULLS from DISTINCT results. I agree that it's unfortunate, if true. There *is* a workaround. You can always throw in a restriction, like WHERE XXX IS NOT NULL I admit that this has the flavor of a kluge, but then so does the Pick construct of: age > 90 and age # In both cases programmers are working around the language rather than with the language. > I suppose sorting is defined in a similar manner? "NULLs are collected > at the top in an ascending sort, and at the bottom in a descending sort, > but we don't actually *compare* them to anything. Oh no." > > > The unique constraint behaviour depends on how you define it. Sorting pertains to cursors (read: lists) rather than sets. If a select forms a set with a NULL in it, then I expect ORDER BY to put it in the cursor somewhere.