CODEWITHSUNDEEP
sqldatabasejoinsubquery
dfritsi
dfritsi

Reputation: 1232

JOIN or subquery is faster?

Hy! I just want to ask a simple question. If we have tables like

and I want to get my "friends" then which query is faster?

SELECT DISTINCT UG1.user_id
           FROM user_group AS UG1
          WHERE UG1.group_id IN ( SELECT UG2.group_id
                                    FROM user_group UG2
                                   WHERE UG2.user_id = 87 )

SELECT DISTINCT UG1.user_id
           FROM user_group UG1
           JOIN user_group UG2 ON UG1.group_id = UG2.group_id
          WHERE UG2.user_id = 87

If the answer is database specific then on which database which method is faster?

Upvotes: 2

Views: 1065

Answers (1)

Robbie Dee
Robbie Dee

Reputation: 1977

The short answer: pull an execution plan for each and have a look

The long answer: It depends on a number of things - indexes, table stats, optimizer algorithm, caching, physical architecture, database size etc etc etc

Upvotes: 3

Related Questions