Convert inner select query to join in MySQL

userUQRl1 0
Viewed 48

I want to convert my SQL query to Join.
I have two tables.

table: MATCH

ID Match_Id User_Id
1 2 1
2 3 1

table: BLOCK

ID Block_Id User_Id
1 2 1

Now from the above tables, I want to get the list of MatchIds which are not blocked.

I solve this problem using nested select queries, But I want to solve this problem using the JOINs. I prepared the below SQL and it is working.

SELECT M.Match_Id
  FROM match M
 WHERE M.user_id = '1'
   AND M.match_id NOT IN
       (SELECT B.block_id 
          FROM block B 
         WHERE B.block_id IS NOT NULL);
mysql sql
1 Answers

If you do a left join on the block table and look for null records on the join, that is effectively a NOT EXISTS query

SELECT M.Match_Id
FROM match M
LEFT JOIN Block B ON B.block_id == M.match_id
WHERE M.user_id = '1' 
AND B.Id IS NULL

Related question

sreimer0
Related