sub query or join?

Question

I am wondering if anyone can explain to me when you would use a sub query and when you would use a join.

for example.

I have this query:

SELECT * from contacts where id in (select contactId from contactgrouplink where groupId = 1);

What would the benefit be of a join over this sub query?

possible duplicate of [Subqueries vs joins](http://stackoverflow.com/questions/141278/subqueries-vs-joins) — Robert Munteanu, Jan 31 '11 at 10:00

score 1 · Answer 1 · edited May 23 '17 at 09:58

1

Look at here well discussed

Subqueries vs joins

edited May 23 '17 at 09:58

Community

1
1

answered Jan 31 '11 at 09:36

Shakti Singh

84,385
21
134
153

FeRtoll · Accepted Answer · 2011-01-31T09:49:24.757

0

select * from contacts x, contactgrouplink y where x.id=y.contactId and y.groupId=1

Use EXPLAIN just before each of those query's... to see what query does!

edited Jan 31 '11 at 09:49

answered Jan 31 '11 at 09:33

FeRtoll

1,247
11
26

Hmm, so what is this query doing (I like it btw). Is it a join, or something different? – Hailwood Jan 31 '11 at 09:36
1

i dont know what is it i dont watch for famous names and standards bla bla... i make a query and then test it 1000 times untill i find best and fastes solution for it... like this one almost all querys works best... yes its some kind of join by aliases of tables! duno someone who read books will explain you that! :P I am based on experiance not on books! :D – FeRtoll Jan 31 '11 at 09:41

score 0 · Answer 3 · answered Jan 31 '11 at 09:39

Do an EXPLAIN, my rule of thumb is to try to get rid of DEPENDENT SUBQUERY since that means for each row in the outer SQL statement, a query is executed. Also, you can try to implement it as a join and see how many rows each version would examine and make the call from there.

score 0 · Answer 4 · answered Jan 31 '11 at 09:43

0

In my knowledge, Sub-query is batter than join . I'm also use Sub-query Becoz "Join" is Effecting performance. (According My-Sql Preference)

answered Jan 31 '11 at 09:43

Manish Trivedi

3,481
5
23
29

This is probably true for other RDBMS since subqueries are easier to read and optimizers do their job well; however for mySQL, at least until 6.0 comes around, joins are a lot more efficient in many cases (http://datacharmer.blogspot.com/2008/09/drizzling-mysql.html) – Clement P Jan 31 '11 at 09:46
Version 6.0 should have been released 1 or 2 years ago and development has been stopped. 5.5 has taken some parts of the codebase and the next 6.0-release (somewhere in the future) will be different from the old 6.0-project. – Frank Heikens Jan 31 '11 at 10:29

sub query or join?

4 Answers4