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

Bob,

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

 >SELECT item_name
 >FROM product_table
 >WHERE prod_id not in (select prod_id from sale_table);

These two queries are logically equivalent. Both work. The first will 
usually be faster.

PB



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:
>
> SELECT item_name FROM product_table WHERE prod_id not in (select 
> prod_id from sale_table);
>
>
>


-- 
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