Which of these queries is preferable?

Question

I wrote the same query as the subquery and self-join.

Is there an obvious argument for one over the other here?

subquery:

SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR'); 

auto join:

 SELECT p1.prod_id, p1.prod_name FROM products p1, products p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR'; 

Answer 1

This post contains some runtime figures. The poster states:

The first query shows 49.2% of the batch, and the second shows 50.8%, which leads to one to think that the subquery is a little faster.

Now I started Profiler and executed both requests. It takes a first request of more than 92,000 reads to complete, but for one with a join, only 2300 is needed, making me believe that the inner join is much faster.

There are conflicting answers:

My rule: use only JOIN if you need to get the column out of the table you are joining; otherwise use subqueries.

and this:

The connection should always be faster - theoretically and realistically. subqueries - especially correlated - can be very difficult to optimize. If you think about it, you will see why - technically, a subquery can be executed once for each line of an external query - blech!

I also agree with Madhivanan that if a sub request returns anything but one value, your main request will not be executed, so use IN .

Answer 2

The first query may cause an error if the subquery returns more than

The second request does not match ANSI

So, it is better to use the ANSI style

 SELECT p1.prod_id, p1.prod_name FROM products as p1 inner join products as p2 on p1.vend_id = p2.vend_id WHERE p2.prod_id = 'DTNTR'; 

Answer 3

If you do not need any of the columns from the JOINed table, then using a subquery is usually preferable, although this depends on the type of RDBM. Instead, use the IN clause:

 SELECT prod_id, prod_name FROM products WHERE vend_id IN (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');