On Tuesday, March 26, 2013 5:19:52 PM UTC+2, Michael Manfre wrote: > > > > On Tue, Mar 26, 2013 at 10:23 AM, Tim Chase > <django...@tim.thechases.com<javascript:> > > wrote: > >> EXISTS also has some nice features >> like the ability to do testing against multiple columns, i.e., you >> can't do something like >> >> select * >> from tbl1 >> where (col1, col2) in ( >> select a, b >> from tbl2 >> ) >> >> but that's a simple EXISTS query. >> > > Agreed, EXISTS is more flexible and at least to me, often easier to read, > format, and maintain. Assuming this is implemented with a database feature, > I'll most likely enable the EXISTS change for django-mssql. My main > objections to a blanket change is to not have the specific behaviors of one > database dictate how every other database must behave. Some one needs to be > the voice of 3rd party database backends and it appears for the time being, > I'm wearing that hat. >
One of the main reasons for the change is that EXISTS allows for queries that are impossible with IN. In addition EXISTS semantics regarding NULLs is wanted. And, at least PostgreSQL performs better with NOT EXISTS than NOT IN. Granted, the better performance on PostgreSQL is perhaps the most important reason for me, but this change is not only about that. The main reason for this thread was to find out if there are some databases where performance of EXISTS is worse than IN. The DB feature approach seems good because it allows investigating performance characteristics one DB at time. > >> > > MSSQL is a 2nd-class citizen in the Django world, so I'm +1 >> > >> > Reasoning like that helps to keep it in its place. >> >> MSSQL's lack of certain core features is what does that. >> OFFSET/LIMIT provided by pretty much every other DB vendor? Use >> baroque hacks to get the same functionality. > > > The non "baroque hack" way of doing limit offset is with nested selects. > Maybe someday the non-standard LIMIT/OFFSET keywords will get added to the > standard (I truly hope this happens) so Oracle, MSSQL, DB2, and Informix > could share SQL with postgres and mysql without needing to mangle it. > Hmmh, this means Oracle, MSSQL, DB2 and Informix are doing more or less the same thing for limit/offset support? If so, then having a more generic approach to this problem than having a custom compiler per backend might be worth it... BTW there is already something like LIMIT and OFFSET in SQL 2008 standard. The syntax is different than LIMIT/OFFSET, and supported only by some vendors... See https://en.wikipedia.org/wiki/Select_%28SQL%29#FETCH_FIRST_clause - Anssi -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
