Re: Which query is more correct?

On Sat, Nov 21, 2009 at 2:52 PM, Nathan Rixham <nrixham@gmail.com> wrote: > Rick Pasotto wrote: >> On Fri, Nov 20, 2009 at 04:41:58PM -0600, LAMP wrote: >>> Hi, >>> I need to pull all records from the table Registrants they are NOT >>> in the table ToBeRecleared >>> >>> Registrants.Reg_ID is PK >>> ToBeRecleared.tbrc_Reg_ID is PK >>> >>> Which query is more correct? >>> >>> SELECT r.* >>> FROM registrants r >>> where r.reg_status=1 AND r.reg_id NOT IN (SELECT tbrc_reg_id FROM >>> toberecleared) >>> >>> >>> SELECT r.* >>> FROM registrants r >>> where r.reg_status=1 AND (SELECT count(*) FROM toberecleared where >>> tbrc_reg_id=r.reg_id) = 0 >>> >>> I checked explain of bot queries - but can't "read" them.  :-) >> >> SELECT t1.* >> FROM registrants t1 >> LEFT JOIN ToBeRecleared t2 on t1.reg_id = t2.tbrc_reg_id >> where t2.tbrc_reg_id is NULL >> > > ^^^ what rick said; the left join with where null is the "correct" one > In my book the "correct" one is anything that gives you the "correct" answer. After that the question is "which is the better one?" That said, Rick's does look pretty cool.