CODEWITHSUNDEEP
mysql
Hide
Hide

Reputation: 3317

MySQL join two tables without NOT IN

Two tables users, relationships in my db.

CREATE TABLE users(
id int primary key auto_increment,
nickname varchar(20),
is_active TINYINT
)

CREATE TABLE relationships(
id int primary key auto_increment,
user_id int,
target_user_id int,
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(target_user_id) REFERENCES users(id)
)

mysql> select * from users;
+----+----------+-----------+
| id | nickname | is_active |
+----+----------+-----------+
|  1 | hide     |         1 |
|  2 | john     |         1 |
|  3 | ben      |         0 |
|  4 | test     |         1 |
|  5 | gogo     |         1 |
+----+----------+-----------+

mysql> select * from relationships;
+----+---------+----------------+
| id | user_id | target_user_id |
+----+---------+----------------+
|  1 |       1 |              2 |
|  2 |       1 |              4 |
+----+---------+----------------+

I have to extract users.id with certain condition.

I will explain in case of users.id = 1

  1. users.is_active=1
  2. user who does not have relationships via relationships table. you know that in current relationships table, user_id = 1 has 2 rows that target_user_id = 2 and 4. So query result does not contain user_id = 2 and 4.

Using NOT IN, it is pretty simple.

SELECT id FROM users WHERE is_active=1 AND id NOT IN(SELECT target_user_id FROM relationships WHERE user_id=1)

RESULT : 1, 5

Note that there is huge rows in users and relationships.

If I using NOT IN with subquery, it will occur performance issue.

So I think I have to join with foreign key but I don't know how to make query exactly.

Any suggestion, appreciate.

Thanks.

Upvotes: 0

Views: 51

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: I am sure LEFT JOIN with IS NULL approach will definitely work for you

SELECT u.id 
FROM users u
LEFT JOIN relationships r ON r.target_user_id = u.id
    AND r.user_id = 1
WHERE u.is_active=1 AND r.target_user_id IS NULL

Upvotes: 1

fifonik
fifonik

Reputation: 1606

Nothing wrong with your query. MySQL should be able to use your index.

However, you can also use left join:

SELECT
    users.id
FROM
    users
    LEFT JOIN relationships ON (
            users.id = relationships.target_user_id
/*
        -- un-comment if filtering by user is needed
        AND relationships.user_id = 1
*/
    )
WHERE
        users.is_active=1
    AND relationships.target_user_id IS NULL
    -- addition filtering can be here

UPDATE: If you filtering by user, you can try to add composite index (user_id, target_user_id) to relationships table (columns should be in this order)

Upvotes: 0

Related Questions