Re: Which query is more correct?
| From: | Rick Pasotto | Date: | Fri, 20 Nov 2009 23:38:04 +0000 |
| Subject: | Re: Which query is more correct? | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-300003@lists.php.net to get a copy of this message | ||
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
--
"Every major horror of history was committed in the name of an
altruistic motive." -- Ayn Rand, The Fountainhead, 1943
Rick Pasotto rick@niof.net http://www.niof.net