CODEWITHSUNDEEP
mysqlsqljoinsubquery
Zulu Irminger
Zulu Irminger

Reputation: 442

Replacing Subqueries with Joins in MySQL

I have the following query:

SELECT PKID, QuestionText, Type 
FROM Questions 
WHERE PKID IN (
    SELECT FirstQuestion 
    FROM Batch 
    WHERE BatchNumber IN (
        SELECT BatchNumber 
        FROM User 
        WHERE RandomString = '$key'
    )
)

I've heard that sub-queries are inefficient and that joins are preferred. I can't find anything explaining how to convert a 3+ tier sub-query to join notation, however, and can't get my head around it.

Can anyone explain how to do it?

Upvotes: 7

Views: 12035

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

In order to do this correctly, you need distinct in the subquery. Otherwise, you might multiply rows in the join version:

SELECT q.PKID, q.QuestionText, q.Type 
FROM Questions q join
     (select distinct FirstQuestion
      from Batch b join user u
           on b.batchnumber = u.batchnumber and
              u.RandomString = '$key'
     ) fq
     on q.pkid = fq.FirstQuestion

As to whether the in or join version is better . . . that depends. In some cases, particularly if the fields are indexed, the in version might be fine.

Upvotes: 0

Jordan Parmer
Jordan Parmer

Reputation: 37174

select
    q.pkid,
    q.questiontext,
    q.type
from user u
join batch b
    on u.batchnumber = b.batchnumber
join questions q
    on b.firstquestion = q.pkid
where u.randomstring = '$key'

Since your WHERE clause filters on the USER table, start with that in the FROM clause. Next, apply your joins backwards.

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16351

Try :

SELECT q.PKID, q.QuestionText, q.Type 
FROM Questions q
INNER JOIN Batch b ON q.PKID = b.FirstQuestion
INNER JOIN User u ON u.BatchNumber = q.BatchNumber
WHERE u.RandomString = '$key'

Upvotes: 0

John Woo
John Woo

Reputation: 263703

SELECT  DISTINCT a.*
FROM    Questions a
        INNER JOIN Batch b
            ON a.PKID = b.FirstQuestion
        INNER JOIN User c
            ON b.BatchNumber = c.BatchNumber
WHERE   c.RandomString = '$key'

The reason why DISTINCT was specified is because there might be rows that matches to multiple rows on the other tables causing duplicate record on the result. But since you are only interested on records on table Questions, a DISTINCT keyword will suffice.

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 6

Related Questions