You're right. A join is often more efficient than a subselect especially
if you have good indices set up. I believe the "IS NULL" will also get
optimized away in your query. In this example I personally like the
subselect syntax for it's explicitness if the speed difference is negligible.
For large data sets I would definetly go with your solution of using an
outer join.
Waldemar
On Mon, 23 Oct 2006, Jerry Schwartz wrote:
Is a sub-select more efficient than an outer join?
SELECT cust_id FROM customers LEFT JOIN orders on customers.cust_id =
orders.cust_id WHERE orders.cust_id IS NULL;
Or am I missing something (as usual)?
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-----Original Message-----
From: Waldemar Jankowski [mailto:[EMAIL PROTECTED]
Sent: Friday, October 20, 2006 1:53 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: help with query: select customers that ARO NOT
in orders table
On Fri, 20 Oct 2006, [EMAIL PROTECTED] wrote:
hi to all,
I have table customers (PK cust_id)
I have table orders (PK order_id, FK cust_id)
I need query that will selecct all customers from
'customers' they don't
have any order, there is not their cust_id in 'orders'.
couls somebody help me?
thanks.
-afan
I think the most explicit way is with a sub select:
select cust_id from customers where
cust_id not in
(select cust_id from orders);
-w
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
