MySQL: How to LEFT JOIN WHERE IS NULL (user1 blocks user2 OR upside down)?

Question

I have a table user and in addition INNER JOIN it with table cities and a few others already. Now I also have the following table:

blocked_user
--------------------------------------------------------
id | user1_id | user2_id | timestamp

How can I use LEFT JOIN WHERE IS NULL, so both user combinations (user1 blocked user2 or user2 blocked user1) are excluded from the search results?

user
--------------------------------------------------------
id | username | ...

Thank you very much in advance for you help!

What do `user1_id` and `user2_id` represent? You should consider renaming those fields. — We Are All Monica, Jun 12 '12 at 13:44

score 2 · Answer 1 · answered Jun 12 '12 at 13:46

In this case I guess the keyword NOT EXISTS would be adecuate, semantically at least.

SELECT * FROM user u
WHERE NOT EXISTS (
    SELECT * FROM blocked_user b 
    WHERE 
        b.user1_id = u.id 
    OR  b.user2_id = u.id
)

There's also indeed the option of using:

SELECT * FROM user u LEFT JOIN blocked_user b ON b.user1_id = u.id 
                                              OR b.user2_id = u.id
WHERE b.id IS NULL

But regarding to the performances I don't know which one is more efficient.

rgds.

We Are All Monica · Accepted Answer · 2012-06-12T14:39:15.830

I'm guessing that you have a current user (the user who is performing the request) and you want to hide all users they have blocked or all users who have blocked them. I'm also assuming that user1_id is the user who did the blocking, and user2_id is the user they blocked. You should edit your question to make those points more clear.

If that's right, here's what I would do:

SELECT id, username

FROM user

LEFT JOIN (
    SELECT DISTINCT user2_id AS blockee_id
    FROM blocked_user
    WHERE user1_id = :current_user_id
) this_user_blocked
ON user.id = this_user_blocked.blockee_id

LEFT JOIN (
    SELECT DISTINCT user1_id AS blocker_id
    FROM blocked_user
    WHERE user2_id = :current_user_id
) blocked_this_user
ON user.id = blocked_this_user.blocker_id

WHERE this_user_blocked.blockee_id IS NULL
AND blocked_this_user.blocker_id IS NULL

I think it makes sense to use two separate LEFT JOIN ... WHERE ... IS NULL constructs because you are really checking for two different situations.

Hi jnylen! Thank you very very much for your help. Unfortunately I get an error `#1054 - Unknown column '2' in 'group statement'`? — Chris, Jun 12 '12 at 14:23
Updated to fix that error and that comment about separate joins. — We Are All Monica, Jun 12 '12 at 14:38
wow, thank you so much jnylen! it is working like charm... so fast despite millions of rows in both tables! thanks again! — Chris, Jun 12 '12 at 15:02

MySQL: How to LEFT JOIN WHERE IS NULL (user1 blocks user2 OR upside down)?

2 Answers2