List:General Discussion« Previous MessageNext Message »
From:Bob Gailer Date:February 13 2006 3:15am
Subject:Re: How to select data if not in both tables?
View as plain text  
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
>
>


Thread
How to select data if not in both tables?Grant Giddens11 Feb
  • Re: How to select data if not in both tables?Peter Brawley11 Feb
    • Re: How to select data if not in both tables?Bob Gailer12 Feb
      • Re: How to select data if not in both tables?Michael Stassen12 Feb
        • Re: How to select data if not in both tables?Bob Gailer13 Feb
          • Re: How to select data if not in both tables?Peter Brawley13 Feb
      • Re: How to select data if not in both tables?Peter Brawley12 Feb
    • Re: How to select data if not in both tables?Grant Giddens12 Feb