Use multiple tables for MySQL joins and avoid using subquery methods

Question

My query in fiddle is 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 inner query to join?

Answer 1

Your subquery is not a dependent subquery, but independent. That is, they do not reference columns in your notification table, only columns in their own table.

So there is no performance issue here.

Answer 2

You can express subqueries as union queries and compare execution plan statistics. Looking at the output in fiddle, 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 to express it is to use 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)
);