CODEWITHSUNDEEP
mysql
Ankit Virani
Ankit Virani

Reputation: 135

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

Upvotes: 2

Views: 138

Answers (4)

DharmasriS
DharmasriS

Reputation: 63

this might work

 SELECT u.id, u.name, u.avatar, u.slug, u.location
    FROM user_registration u LEFT JOIN followers f
    ON u.id=f.user_id
    WHERE u.id <>'3' AND f.follower_id <>'3'
    order by u.id;

Upvotes: 0

Unix One
Unix One

Reputation: 1181

Here's a join query that doesn't use a sub-select:

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

Note that if you use a sub-select (as in few other answers), even if you're joining its result, MySQL is still likely using internal temporary tables (that lack any indexes) to perform the joins. If you join the actual tables, you retain all the benefits of joining using existing indexes. You can always run EXPLAIN to see what indexes are getting used.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

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

Upvotes: 1

Hoven
Hoven

Reputation: 573

SELECT u.id, u.name, u.avatar, u.slug, u.location
FROM user_registration as u
LEFT JOIN followers ON followers.follower._id = u.id
AND u.id <>'3'

Upvotes: 0

Related Questions