Replacing nested query with join

Question

Based on some googling and this question here Join vs. sub-query, it seems that its recomended to use join instead of nested query, hence I'm trying to enhance what I'm doing.

This is something like a file system, imagine a few tables below.

user               group           folder
==========         ==========      ==========  
id   name          id   name       id   name 
==========         ==========      ==========
 1   Jack           1   HR          1   Staff Policies                             
 2   James          2   Sales       2   Contracts                              
 3   Joe            3   IT          3   Documentations

And linking table below

group_user
===================
groupId    userId
===================
1           1  (Jack works in HR)                                                                                                                
1           2  (James works in HR)                                                                                       
3           3  (Joe works in IT)                                                                                                          
3           1  (Jack also works in IT)                                                                                           

group_folder
====================
groupId    folderId
====================
1          1   (HR can access Staff Policies)
1          2   (HR can access Contracts)                                                                            
3          3   (IT can access Documentations)
1          3   (HR can also access Documentations)

The goal is to query out which folder can be accessed by Jack, hence I'm using below:

SELECT   f.id,
         f.name
FROM     folder f
WHERE    f.ID IN (
                  SELECT gf.folderId
                  FROM   group_folder gf 
                  WHERE  gf.groupId in (
                                        SELECT gu.groupId
                                        FROM   group_user gu
                                        WHERE  gu.userId = 1
                                       )
                  );

The above basically can get me exactly what I needed, but such nested query may cause performance issue in future. Does anyone has any recommendation on how to make this a join instead of nested query?

score 1 · Accepted Answer · answered Apr 25 '19 at 06:54

You basically put everything together via INNER JOIN with the same constraints you used with IN in your query, but here use ON instead. Then finally you can add a WHERE clause to select only those rows you need:

SELECT f.id, f.name
FROM folder f
INNER JOIN group_folder gf ON f.ID = gf.folderID
INNER JOIN group_user gu ON gf.groupID = gu.groupID
WHERE gu.userId = 1

score -1 · Answer 2 · answered Apr 25 '19 at 07:13

-1

From your query you will get folders which userid= 1 have, actual query needs group by clause, try this one, which gets same data which your query gets

SELECT   f.id,f.name
FROM  folder f             
inner join group_folder gf on gf.folderId = f.id
inner join group_user gu on gu.groupid = gf.groupId
WHERE  gu.userId = 1
group by f.id

answered Apr 25 '19 at 07:13

Jay Borkhetariya

86
1
5

This will not work with `sql_mode=ONLY_FULL_GROUP_BY`, which is default in recent MySQL versions. – Ulrich Thomas Gabor Apr 25 '19 at 08:52

Replacing nested query with join

2 Answers2