Re: Which query is more correct?

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