Archive for the ‘MySQL’ Category

Use join instead of subqueries

No Comments »

As a programmer, subqueries are something that you can be tempted to use and abuse. Subqueries, as show below, can be very useful:

SELECT a.id,
(SELECT MAX(created)
FROM posts
WHERE author_id = a.id)
AS latest_post FROM authors a

Although subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.

SELECT a.id, MAX(p.created) AS latest_post
FROM authors a
INNER JOIN posts p
ON (a.id = p.author_id)
GROUP BY a.id