Re: Set Operation or Outer Join?

Jack Wang wrote:

>All,
>
>In relational database class, I was taught that we should use the following
>query for answering questions like "list of customers who do not have any
>orders".
>
>Select cid, CustomerName, CustomerAddress from customer
>Where cid IN
>(
>Select cid from customer
>Minus
>Select cid from order
>)
>
>
>But in practice, I often used Outer Join to perform the task as follows,
>
>SELECT cid, CustomerName, CustomerAddress
> FROM customers, orders
> WHERE customers.cid = orders.cid (+)
> AND orders.cid IS NULL
>
>
>Which one is preferred in terms of efficiency and easy to understand/write?
>
>Thanks.
>
>

I run a class at the U for students in which I prove to them, conclusively I might add, the following maxim:

You must try it both ways and determine which is best with your tables, your indexes, the quantity of
your data, the cardinality of your data, etc.

There are no rules: Only helpful hints as to where to start. But generally speaking ... I'd not start with a join if a non-join solution was available.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)