MySQL where subselect on multiple fields

Question

I'm trying to do a single MySQL query to select data inside a third party function, the problem I have however they only pass me the ID. I need to use that ID to select all the related records.

Currently the MySQL statement looks something like this:

SELECT h.id, h.notification_id, h.status, d.detail 
FROM headers h, details d 
WHERE h.host_id = (SELECT host_id FROM headers WHERE id = '{$rowid}') 
AND h.service_id = (SELECT service_id FROM headers WHERE id = '{$rowid}') 
AND h.instance = (SELECT instance from headers where id = '{$rowid}')
AND h.id = d.header_id;

Now this works, but I'm thinking the poor MySQL engine is running 3 subqueries because I don't have 3 pieces of information available to me.

So I was wondering if there a way to run this with one query and one subquery to save the load on the database?

Thanks in advance.

Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. — Juan Carlos Oropeza, Mar 31 '17 at 15:22

score 3 · Answer 1 · answered Mar 31 '17 at 15:16

3

You can use SELF JOIN to remove the multiple sub-queries.

Try this:

SELECT h.id, h.notification_id, h.status, d.detail 
FROM headers h 
INNER JOIN details d ON h.id = d.header_id 
INNER JOIN headers h2 ON h.host_id = h2.host_id AND h.service_id = h2.service_id AND h.instance = h2.instance 
WHERE h2.id = '{$rowid}'

answered Mar 31 '17 at 15:16

Saharsh Shah

28,687
8
48
83

beat me too it. `JOIN` will be much faster. – Cfreak Mar 31 '17 at 15:17

Thorsten Kettner · Accepted Answer · 2017-03-31T15:39:02.990

2

You can work with tuples (i.e. rather than looking up host_id and service_id and instance you'd look up the combination (host_id, service_id, instance)):

SELECT h.id, h.notification_id, h.status, d.detail 
FROM headers h
JOIN details d ON d.header_id = h.id
WHERE (h.host_id, h.service_id, h.instance) = 
  (SELECT host_id, service_id, instance FROM headers WHERE id = '{$rowid}') 
;

This puts criteria wehere it belongs: in the where clause (just as in your query). You select from headers and details where the combination (host_id, service_id, instance) matches the record with the given ID.

edited Mar 31 '17 at 15:39

answered Mar 31 '17 at 15:34

Thorsten Kettner

89,309
7
49
73

Which is faster a join or a tuple? – trevrobwhite Mar 31 '17 at 15:38
This depends on the DBMS's optimizer. A good optimizer would get to the same execution plan for both queries. I strive to have my queries as readable as possible (which often means only selecting from the tables I want to see data from and using `[NOT] IN` or `[NOT] EXISTS` rather than joining). I only change this if I really have performance issues. I don't think that above query would give you performance issues, so it would be my preference. At last it often boils down to exactly this: personal preference. – Thorsten Kettner Mar 31 '17 at 15:42
Look at it like this: Which query would be easier for you to understand, if you looked at them in three or four years :-) – Thorsten Kettner Mar 31 '17 at 15:45
By the way: as you can get several headers with several details with the queries you might want to apply an `ORDER BY` clause, to make sure that you see the records per order (and not a detail for order 1, then a detail for order 2, then a detail for order 1 again, etc.) – Thorsten Kettner Mar 31 '17 at 15:48

MySQL where subselect on multiple fields

2 Answers2