Ankit Virani Ankit Virani - 4 months ago 7
MySQL Question

How to convert this query into join query

Please convert the following query into join query without using subquery in mysql

SELECT u.id, u.name, u.avatar, u.slug, u.location
FROM user_registration as u
WHERE u.id <>'3' AND u.id NOT IN (SELECT f.user_id FROM followers as f WHERE f.follower_id ='3')
order by id

Answer

One way to port your query over to a join would be to left join user_registration with a subquery on followers. The criteria for a record to be retained is that the user registration id is not '3', and also that record did not match to anything in the join with the subquery on followers.

SELECT t1.id, t1.name, t1.avatar, t1.slug, t1.location
FROM user_registration t1
LEFT JOIN
(
    SELECT f.user_id
    FROM followers f
    WHERE f.follower_id = '3'
) t2
    ON t1.id = t2.user_id
WHERE t1.id <> '3' AND t2.user_id IS NULL
ORDER BY t1.id

I think the following query should also work:

SELECT t1.id, t1.name, t1.avatar, t1.slug, t1.location
FROM user_registration t1
LEFT JOIN followers t2
    ON t1.id = t2.user_id AND t2.follower_id = '3'
WHERE t1.id <> '3' AND t2.user_id IS NULL
ORDER BY t1.id