What the most efficient way to make a three tables JOIN with a WHERE clause in postgreSQL

Question

I'm working with postgreSQL 9.3.

I'd like to say that my question is a bit subjective but I didn't find any related questions or articles...

I have three tables as following:

artists:

+--------+----------+
|artistId|   name   |
+--------+----------+
| 1      | James    |
| 2      | Jack     |
| 3      | Henry    |
| 4      | Tom      |
+--------+----------+

genres:

+---------+-----------+
| genreId |    name   |
+---------+-----------+
|   1     |   rock    |
|   2     |   dub     |
+---------+-----------+

and the junction table artistsGenres:

+--------+---------+
|artistId| genreId |
+--------+---------+
|      1 |     1   |
|      2 |     2   |
|      3 |     1   |
|      4 |     2   |
+--------+---------+

I want to get all the artists by genre name. For the moment I do it like this:

SELECT a.*
FROM artistsGenres aG
  INNER JOIN artists a ON a.artistId = aG.artistId
WHERE aG.genreId = (SELECT genreId FROM genres WHERE name = 'dub');

I'm wondering if there is a more efficient way to do it?

Looks like your query is incorrect, you are matching aG.artitsId against genres.genreId. — Eelke, Apr 04 '15 at 11:26
Can an artist have more than one genre? (for instance, could a artistsgenres row exist with {artistId=1, genreId=2} ?) — wildplasser, Apr 04 '15 at 11:51

score 3 · Accepted Answer · answered Apr 04 '15 at 11:30

In general it is better not to use a subquery if it can be done with a join. However with a bit of luck the query planner rewrites subquery as a join for you! Here is how it would look if you did it yourself.

SELECT a.*
FROM artistsGenres aG
  INNER JOIN artists a ON a.artistId = aG.artistId
  INNER JOIN genres g ON g.genreId=aG.genreId
WHERE g.name='dub'

In which order you do the joins doesn't matter much as postgresql will reorder them to what it thinks works best.

score 0 · Answer 2 · answered Apr 04 '15 at 12:27

0

SELECT *
FROM artists a
WHERE EXISTS (
  SELECT 1
  FROM artistsGenres aG
  JOIN genres g ON g.genreId = aG.genreId
  WHERE aG.artistId = a.artistId 
  AND g.name = 'dub'
  );

answered Apr 04 '15 at 12:27

wildplasser

43,142
8
66
109

Thanks for your answer, but your query seems to be a little bit slower than the query of Eelke (but my test table is very small, it's maybe the reason). – Simon Apr 04 '15 at 13:53
For non-trivial cases, it can only be faster. Do note that the queries will produce different results: in the case of more-than-one genre per artist (this would also need the selection to allow multiple choices, like in `AND g.name IN( 'country', 'dub')` , @Eelke's query will yield multiple result rows per artist, mine one (or zero) rows per artist. – wildplasser Apr 04 '15 at 14:55
Consider adding a couple of lines of explanation in brief as to why this is the best(and under what circumstances). Links to good references will help as well. – TheCodeArtist Apr 05 '15 at 04:06
@wildplasser: how could Eelke's query yield multiple result rows per artist (since one artist can have several genres but can't have several times the same genre) ? – Simon Apr 06 '15 at 06:41

What the most efficient way to make a three tables JOIN with a WHERE clause in postgreSQL

2 Answers2