Use multiple tables for MySQL joins and avoid using subquery methods-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Use multiple tables for MySQL joins and avoid using subquery methods

P粉442576165 2023-09-07 17:38:48

398

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?

P粉442576165

reply all (2)

P粉5672810152023-09-08 16:30:03 2 floor

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

So there is no performance issue here.

Like+0

Add Reply

P粉5761849332023-09-08 15:26:05 1 floor

You can express subqueries as union queries and compare execution plan statistics. Looking at the output infiddle,unionseems 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 useexists

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

Like+0

Add Reply