Multiple SELECT subqueries to the same table into one

Question

There's a table message and a table sender, and I want to retrieve all "message information" associated to a specific id, along with the sender name and email. I have this query:

SELECT
    a.id,
    a.subject,
    a.body,
    a.sender_id,
    (
        SELECT b.name
        FROM sender b
        WHERE b.id = a.sender_id
        LIMIT 1
    ) AS sender_name,
    (
        SELECT b.email
        FROM sender b
        WHERE b.id = a.sender_id
        LIMIT 1
    ) AS sender_email,
    a.sent_time
FROM message a
WHERE a.id = <id>
LIMIT 1;

It works, but it has to perform two different SELECT subqueries to the same table. In this case (which is an extremely simplified example) it probably doesn't hurt the performance at all, but in a real-life scenario where a lot of fields have to be retrieved from an external table, the best way to do it is using a JOIN statement? Isn't it possible to have a SELECT subquery that retrieves many fields?

I'm using MySQL with MyISAM storage engine in case it matters.

score 4 · Accepted Answer · answered Aug 20 '13 at 17:06

4

Just join to the sender tables once in the FROM clause.

SELECT  a.id,
        a.subject,
        a.body,
        a.sender_id,
        b.name,
        b.email,
        a.sent_time
FROM    message a
INNER JOIN
        sender b
ON      b.id = a.sender_id       
WHERE a.id = <id>
LIMIT 1;

answered Aug 20 '13 at 17:06

Declan_K

6,726
2
19
30

I thought doing a JOIN was overkill in this case, but I guess it's the best choice. – federico-t Aug 20 '13 at 18:45

score 0 · Answer 2 · answered Aug 20 '13 at 17:06

0

Try this:

SELECT
    a.id,
    a.subject,
    a.body,
    a.sender_id,
    b.name AS sender_name,
    b.email AS sender_email,
    a.sent_time
FROM message a, sender b
WHERE a.id = <id>
AND b.id = a.sender_id
LIMIT 1;

answered Aug 20 '13 at 17:06

David Starkey

1,840
3
32
48

Please don't recommend non-ANSI JOIN patterns. http://stackoverflow.com/a/1599201/2324286 – Declan_K Aug 20 '13 at 17:08
@Declan_K If you'd like to keep with the link you gave: "all modern query tools will generate ANSI-92". The system will automatically transfer one into the other in most cases. If this syntax is easier, then by all means you are free to use it. – David Starkey Aug 20 '13 at 17:19

score 0 · Answer 3 · answered Aug 20 '13 at 17:08

0

If you really don't want to use a join, you could use concat in your subquery CONCAT(b.name,";",b.email) and later explode the result on ; - but I'd go for join :D

answered Aug 20 '13 at 17:08

Jaak Kütt

2,566
4
31
39

Multiple SELECT subqueries to the same table into one

3 Answers3