justinspegele.com

Wednesday, 01 Oct 2008 11:00 EST

MySQL: Subqueries vs. Joins

MySQL subqueries, supported in v4.1+, allow for SELECT statements to be placed within other statements, giving you a structured query that can be isolated from the rest of the query. This allows you to avoid using complex joins and have more readable, easier to understand code. These advantages come at the expense of code performance, though, as those complex joins are often more efficient than a subquery. For a small-scale database, like one for a personal website or blog, the ease and readability of your code will provide more of a benefit than the small hit in performance.

Let's take a look at a basic example of a subquery. This query selects all players in a baseball league that play for New York teams. The query looks at two tables; players, which contains data on each player in the league (including their name and the team they play for), and teams, which contains data on each team in that league (including the city it plays in).

SELECT players.name
FROM players
WHERE players.team
    IN (SELECT teams.name FROM teams WHERE teams.city='New York');

For those of you who are all about efficiency, here is how you could rewrite this as a JOIN.

SELECT players.name
FROM players, teams
WHERE players.team=teams.name
    AND teams.city='New York';

OK, so in this example, the JOIN might just be little bit easier than the subquery. Trust me, though, as the queries get bigger and you start adding more tables, the subqueries will be much easier to handle.

Let's look at a somewhat more complex example. This query returns the names of every player who has hit a homerun by selecting names from the players table where there is a record of the player hitting a homerun in the hits table.

SELECT 
    players.name,
    (SELECT DISTINCT hits.player_id
     FROM hits
     WHERE hit_type='homerun'
    ) AS hr_hitters
FROM players
WHERE players.id=hits.player_id;

And the same query rewritten as an inner join.

SELECT DISTINCT players.name
FROM players, hits
WHERE players.id=hits.player_id
    AND hits.type='homerun';

Now let's take a look at one final example, containing 3 nested queries. This example selects all players who have hit more than 20 homeruns by taking a COUNT of each players homeruns hit.

SELECT 
    players.name,
    (SELECT
         (SELECT
              player_id,
              COUNT(*) AS count
          FROM hits
          WHERE hit_type='homerun'
         ) AS hr_count,
     FROM hits
     WHERE count>=20
    ) AS hr_hitters
FROM players
WHERE players.id=hits.player_id;

Here's where we really see the benefit of subqueries. This query would be very difficult to write as an inner join because of the third level count. If you're running a small site and can afford a small hit in performance, writing your queries with subqueries instead of joins can save a tremendous amount of development time as well as pain and frustration while you debug your code.

As always, if you have a better technique or a tweak that you think improves on this code, please share it in the comments. Also, if you have a tip, a trick or an idea for my next post, please don't hesitate to contact me.

Wednesday, 01 Oct 2008 11:00 EST

MySQL: Subqueries vs. Joins

Post a comment

Welcome

Friend me

I Click

Books I like

Movies you should watch

Login