[SOLVED] MySQL join with multiple tables without subquery approach

Issue

I have the query in the fiddle as follows.

select * from notification where status = 0 and (
 notif_id in (select notif_id from notif_user where user_id = 1) OR 
 notif_id in (select notif_id from notif_group where group_id = 1))

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=cad284e77218eb37461e60b6308bf85f

The query works as expected. But, will there be any performance issues with the query. Is it possible to convert the inner query with Join?

Solution

You could express the subquery as a union and compare the execution plan statistics. Looking at the output in the fiddle, the union seems to perform slightly better.

select * 
from notification 
where status = 0 and (
 notif_id in (
    select notif_id from notif_user where user_id = 1 union all
    select notif_id from notif_group where group_id = 1
  )
);

Another way of expressing this would be using exists

select * 
from notification n 
where status = 0 and
(
  exists (select * from notif_user nu where nu.user_id = 1 and nu.notif_id = n.notif_id)
  or exists(select * from notif_group ng where ng.group_id = 1 and ng.notif_id = n.notif_id)
);

Answered By – Stu

Answer Checked By – David Marino (BugsFixing Volunteer)