Replacing Subqueries with Joins in MySQL

Question

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 heard that subqueries are inefficient and that joins are preferable. However, I cannot find anything explaining how to convert a subquery to 3+ tiers to join the notation, and I cannot get around it.

Can someone explain how to do this?

Answer 1

 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 DISTINCT was specified is because other tables may have rows that correspond to multiple rows, resulting in a duplicate record as a result. But since you are only interested in the entries in the Questions table, the DISTINCT keyword will suffice.

To learn more about joining, follow the link below:

• Visual representation of SQL joins

Answer 2

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' 

Answer 3

 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 clauses are filtered in the USER table, start with this in the FROM . Then apply your connections back.

Answer 4

To do this correctly, you need distinct in the subquery. Otherwise, you can propagate strings in the connection 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 for improving the version of in or join ., It depends. In some cases, especially if indexes are indexed, the in version can be great.