List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 12 2006 12:38am
Subject:Re: How to select data if not in both tables?
View as plain text  
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.

> 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