CODEWITHSUNDEEP
mysqljoinsubquery
Hailwood
Hailwood

Reputation: 92581

sub query or join?

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?

Upvotes: 1

Views: 350

Answers (4)

FeRtoll
FeRtoll

Reputation: 1267

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!

Upvotes: 0

Manish Trivedi
Manish Trivedi

Reputation: 3559

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

Upvotes: 0

Clement P
Clement P

Reputation: 3273

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.

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86336

Look at here well discussed

Subqueries vs joins

Upvotes: 1

Related Questions