user1330974 user1330974 - 9 days ago 7
SQL Question

Avoiding multiple inner joins to add new column (info) from the same table

I'm wondering if there is a way to bring

name
information for
user_id
,
sender_user_id
and
recipient_user_id
from the schema in this fiddle.

The only way I can think of now is to do this nested inner join, which I feel like is inefficient:

SELECT e.id
,msg_owner
,msg_owner_name
,sender_user_id
,sender_name
,recipient_user_id
,f.NAME AS recipient_name
,msg_body
,created_at
FROM (
SELECT c.id
,msg_owner
,msg_owner_name
,sender_user_id
,d.NAME AS sender_name
,recipient_user_id
,msg_body
,created_at
FROM (
SELECT a.id
,user_id AS msg_owner
,NAME AS msg_owner_name
,sender_user_id
,recipient_user_id
,msg_body
,created_at
FROM messages AS a
INNER JOIN users AS b ON a.user_id = b.id
) AS c
INNER JOIN users AS d ON c.sender_user_id = d.id
) AS e
INNER JOIN users AS f ON e.recipient_user_id = f.id


Is there any (more efficient) way to bring in
name
values for each of the aforementioned three columns? Thank you for your answers/suggestions!

Answer

Just use joins, not nested joins.

Your way, you force the database to gather back all the results of each inner query, and then having it join to another table. Unless the contents of the inner most query are very limited via a WHERE clause, this will always be bad for performance.

Below is the proper way to use joins. It allows direct use of indexes (if present) to speed things up.

SELECT m.id
    ,owner.id AS msg_owner
    ,owner.name AS msg_owner_name
    ,m.sender_user_id
    ,sender.name AS sender_name
    ,m.recipient_user_id
    ,recipient.name AS recipient_name
    ,m.body AS msg_body
    ,m.created_at
FROM messages m
INNER JOIN users sender
ON sender.id = m.sender_user_id
INNER JOIN users recipient
ON recipient.id = m.recipient_user_id
INNER JOIN users owner
ON owner.id = m.user_id