Which query is more correct?

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. :-)

Thanks,
L

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Which query is more correct?

--=-J6oIT79Nqy7TljxP4/7L
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Fri, 2009-11-20 at 16:41 -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. :-)
>
> Thanks,
> L
>


I'd go with the first, just because thats how I'd code it if I was doing
this myself, as it just makes more sense to the way I write my queries

Thanks,
Ash
http://www.ashleysheridan.co.uk



--=-J6oIT79Nqy7TljxP4/7L--

Re: Which query is more correct?

On Fri, 20 Nov 2009 16:41:58 -0600, LAMP wrote:

> 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. :-)

Like Ashley, I'd probably go with the first one. The other
one seems a little backwards. Out of curiosity, what *does*
EXPLAIN say about them?


/Nisse

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Which query is more correct?

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

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

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

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Which query is more correct?

On Sat, Nov 21, 2009 at 2:52 PM, Nathan Rixham 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=3D1 AND r.reg_id NOT IN (SELECT tbrc_reg_id FROM
>>> toberecleared)
>>>
>>>
>>> SELECT r.*
>>> FROM registrants r
>>> where r.reg_status=3D1 AND (SELECT count(*) FROM toberecleared where
>>> tbrc_reg_id=3Dr.reg_id) =3D 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 =3D 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.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Which query is more correct?

--------------060404060005040302070807
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>
>
thanks!
:-)

--------------060404060005040302070807--