Join to replace sub-query

Question

I am almost a novie in database queries. However,I do understand why and how correlated subqueries are expensive and best avoided. Given the following simple example - could someone help replacing with a join to help understand how it scores better:

SQL> select
  2    book_key,
  3    store_key,
  4    quantity
  5  from
  6    sales s
  7  where
  8    quantity < (select max(quantity)
  9                 from sales
 10                 where book_key = s.book_key);

Apart from join,what other option do we have to avoid the subquery.

@IUnknown could you confirm because your question is conflicting, specifically: "Apart from join,what other option do we have to avoid the subquery." — Robert, May 30 '13 at 16:48
I always hear joins being compared to sub-queries - so I am interested in the option that uses JOIN.Being a newbie,it would also help if other solutions(beside JOIN) are suggested,if any. — IUnknown, May 30 '13 at 16:51

score 0 · Answer 1 · answered May 30 '13 at 16:47

0

In this case, it ought to be better to use a windowed-function on a single access to the table - like so:

with s as
(select book_key, 
        store_key, 
        quantity, 
        max(quantity) over (partition by book_key) mq
 from sales)
select book_key, store_key, quantity
from s 
where quantity < s.mq

answered May 30 '13 at 16:47

I think it is more expensive than previous Query – Prahalad Gaggar May 30 '13 at 16:48
@Luv: It shouldn't be; this approach only requires a single access of the table, whereas the original query has one access of the table in the main query, plus one access for every row of the main query table in the sub-query. – May 30 '13 at 16:50

score 0 · Answer 2 · answered May 30 '13 at 19:27

Using Common Table Expressions (CTE) will allow you to execute a single primary SELECT statement and store the result in a temporary result set. The data can then be self-referenced and accessed multiple times without requiring the initial SELECT statement to be executed again and won't require possibly expensive JOINs. This solution also uses ROW_NUMBER() and the OVER clause to number the matching BOOK_KEYs in descending order based off of the quantity. You will then only include the records that have a quantity that is less than the max quantity for each BOOK_KEY.

with CTE as
(
  select 
    book_key, 
    store_key, 
    quantity,
    row_number() over(partition by book_key order by quantity desc) rn
  from sales
)
select 
  book_key, 
  store_key, 
  quantity 
from CTE where rn > 1;

Working Demo: http://sqlfiddle.com/#!3/f0051/1

Possibly rank(), rather than row_number() - what if multiple stores had the same (maximum) number of books? — , May 31 '13 at 10:21

score -1 · Answer 3 · answered May 30 '13 at 16:51

-1

Apart from join,what other option do we have to avoid the subquery.

You use something like this:

SELECT select max(quantity)
INTO @myvar 
from sales
where book_key = s.book_key

select book_key,store_key,quantity
from sales s
where quantity < @myvar

answered May 30 '13 at 16:51

Prahalad Gaggar

11,389
16
53
71

Join to replace sub-query

3 Answers3