CODEWITHSUNDEEP
mysqlsqljoin
0xSina
0xSina

Reputation: 21553

Use JOINS instead of subquery

I have a simple query that uses a subquery:

SELECT pictures.*
FROM pictures
WHERE pictures.user_id IN
    (SELECT follows.following_id
     FROM follows
     WHERE follows.follower_id = 9)
ORDER BY created_at DESC LIMIT 5;

I am wondering, a) How can I remove the sub query and use JOINS instead and b) will there be a performance benefit in using JOINS instead of sub query?

(follows.following_id, follows.follower_id, pictures.user_id are all indexed)

Thanks

Upvotes: 1

Views: 3023

Answers (1)

John Woo
John Woo

Reputation: 263703

SELECT  DISTINCT pictures.*
FROM    pictures
        INNER JOIN follows
            ON pictures.user_ID = follows.following_id
WHERE   follows.follower_id = 9
ORDER   BY pictures.created_at DESC 
LIMIT   5

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

UPDATE

Another way to achieve the same result is by using EXISTS

SELECT  *
FROM    pictures
WHERE   EXISTS
        (
            SELECT  1
            FROM    follows
            WHERE   pictures.user_ID = follows.following_id AND
                    follows.follower_id = 9
        )
ORDER   BY pictures.created_at DESC 
LIMIT   5

Upvotes: 4

Related Questions