On Mon, Mar 27, 2006 at 03:46:17PM +0200, Alban Hertroys wrote: > Jim C. Nasby wrote: > >On Fri, Mar 24, 2006 at 11:25:35AM -0700, Edmund.Bacon@xxxxxxxxxxxxxxxxxxx > >wrote: > ><pet-peeve> > >If you only want to know if something exists, do NOT use count! > > > > > >>test$# (select * from test_dates t1 > >>test$# where EXISTS ( select * from test_dates t2 > >>test$# where (t1.from_date, t1.to_date) overlaps > >>test$# (t2.from_date, t2.to_date)) )$$; > > > > > >On a small dataset you may not notice much difference, but you'll > >certainly see it on a large dataset. > ></pet-peeve> > > Certainly true, I keep telling people here at work. But I was kind of > wondering why you'd "select * from" inside "exists", if you're not going > to use those values. > > I tend to write "where exists (select 1 from", but now I'm curious > whether the planner handles this for you or if there's actually a > difference in meaning? > > I assume it's always better to not let the planner decide these kind of > things. Try to write what you really want, instead of waving hands in > the general direction and make the planner decide what you mean ;) > > Well, this is getting awfully pet-peevish, of course... It's actually highly database-dependant. Many databases know all about EXISTS (SELECT * ...) and can optimize accordingly. Other databases will do better with SELECT 1, and finally some will actually do worse with SELECT 1. I actually have a book that delves into how things like this are handled on different databases, though I don't remember the title off-hand. In any case, any difference from SELECT 1 vs SELECT * will be lost in the noise compared to getting rid of the count(*). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
