From: Peter Brawley
Date: February 13 2006 3:45am
Subject: Re: How to select data if not in both tables?
List-Archive: http://lists.mysql.com/mysql/194835
Message-Id: <43F000C0.3060400@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bob,

There's some discussion of it at 
http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html.

PB

-----

Bob Gailer wrote:
> Michael Stassen wrote:
>> Bob Gailer wrote:
>>> Peter Brawley wrote:
>>>
>>>> Grant,
>>>> >If I want to select all the products that are in the 
>>>> product_table, >but not in the sale_table, how to make the query?  
>>>> The product_table >has all the products, but the sale table is a 
>>>> subset of the product_table.
>>>>   SELECT * FROM product_table p
>>>> LEFT JOIN sale_table s USING (prod_id)
>>>> WHERE s.prod_id IS NULL;
>>>
>>> I have not tested that but I don't think it will work. Try:
>>
>> Why not?  This is the classic LEFT JOIN solution.  It will work in 
>> all versions of mysql.
> Oops. I'm red-faced. Good humbling for my first appearance on this list.
> It can only get better? This is a good lesson in SQL for me. I did not
> know that a where clause could apply to rows in the result.
>
> I haver searched in vain to find a clear definition of WHERE that 
> explains this behavior. Any pointers?
>>
>>> SELECT item_name FROM product_table WHERE prod_id not in (select 
>>> prod_id from sale_table);
>>
>> This will work only in mysql 4.1+, and will almost certainly be 
>> slower, because mysql's optimizer tends to treat the subquery as 
>> dependent, meaning it will be rerun for each and every row of the 
>> product_table.
>>
>> Michael
>>
>>
>
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006