Mysql join with multiple tables without subquery approach-CodePudding

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 perf issues with the query. Is it possible to convert the inner query with Join?

Thanks

CodePudding user response：

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)
);

CodePudding user response：

Your subqueries are not dependent subqueries, they're independent. That is, they don't refer to columns in your notification table, only to columns in their own tables.

So there's no performance problem here.

CodePudding user response：

Here is the same query written as a join without sub-query.
I would, however, expect the performance to be negatively impacted.

select distinct n.*
from notification n
left join notif_user u
on n.notif_id = u.notif_id
left join notif_group g
on n.notif_id = g.notif_id
where status = 0
and (user_id = 1
or group_id = 1);