Below we have some MySql (but it is very generic in its ideas … particularly if you get rid of the `s), using phpMyAdmin, showing a table Join versus the same functionality using a Subquery approach, then each of these with a Having clause, using a Drupal database.
SELECT u.`name` , n.`type` , count( * )
FROM `users` u, `node` n
WHERE u.`uid` = n.`uid`
GROUP BY u.`uid` , n.`type`
ORDER BY n.`uid`, n.`type`;
SELECT (
SELECT u.`name`
FROM `users` u
WHERE u.`uid` = n.`uid`
) AS OurName, n.`type` , count( * )
FROM `node` n
GROUP BY (
SELECT u.`name`
FROM `users` u
WHERE u.`uid` = n.`uid`
), n.`type`
ORDER BY n.`uid`, n.`type`;
SELECT u.`name` , n.`type` , count( * )
FROM `users` u, `node` n
WHERE u.`uid` = n.`uid`
GROUP BY u.`uid` , n.`type`
HAVING count( * ) > 1
ORDER BY n.`uid`, n.`type`;
SELECT (
SELECT u.`name`
FROM `users` u
WHERE u.`uid` = n.`uid`
) AS OurName, n.`type` , count( * )
FROM `node` n
GROUP BY (
SELECT u.`name`
FROM `users` u
WHERE u.`uid` = n.`uid`
), n.`type`
HAVING count( * ) > 1
ORDER BY n.`uid`, n.`type`;
If this was interesting you may be interested in this too.
26 Responses to MySql Join versus Subquery and Having Primer Tutorial