0

I'm currently at university and doing a project where I have queries such as:

Select * from recent_purchases where customer_id in 
(  select customer_id from 
   customers where name like '%john%'
)

I'm not sure if this is the most idiomatic way of doing things or if I'm missing the "correct" way of doing it - it certainly feels a bit clunky. I don't really understand joins yet. Sorry if a stupid question.

Bharat
  • 2,441
  • 3
  • 24
  • 36
Apoxd
  • 1
  • 2
    Most probably you want a JOIN here (to be more specific, an inner join) https://en.wikipedia.org/wiki/Join_(SQL) – jarlh Oct 26 '16 at 10:45
  • 2
    I know that I'm inviting lots of fire in my direction, but I disagree with those suggesting the user of JOIN. Your query is OK for this specific simple need and, performance-wise, I guess will give the best result. You would need to consider JOINing for far more complex queries whose phrasing would be a nightmare using your simple style. This is a personal opinion that many people would disagree. Up to you to select (and verify if you wish). – FDavidov Oct 26 '16 at 10:55
  • 2
    AFAIK, several dbms products optimize joins better than the IN. – jarlh Oct 26 '16 at 12:01
  • I agree with FDavidov. Plus the join is not necessarily the same thing as the sub-query. And most modern optimizers create the same execution plan anyway **if** the queries are equivalent –  Oct 26 '16 at 12:41

1 Answers1

0

Use an INNER JOIN instead of a Sub-Select in the WHERE clause:

Select * 
from recent_purchases rp 
inner join customers c on c.customer_id = rp.customer_id 
where c.name like '%john%'
rbr94
  • 2,227
  • 3
  • 23
  • 39
  • I knew I was missing something obvious, thanks for the response! What is the advantage of this way vs the two selects? – Apoxd Oct 26 '16 at 10:55
  • @Apoxd See this: http://stackoverflow.com/questions/2577174/join-vs-sub-query. Joins are usually faster than sub-selects. But it depends on your usage – rbr94 Oct 26 '16 at 12:01
  • @Apoxd The join does not necessarily return the same thing as the sub-query. Plus: joins are not "usually faster". If the join solution and the sub-query are equivalent most modern optimizers will use the same execution plan anyway –  Oct 26 '16 at 12:40