What kind of join do I need?

Question

I have a votes table:

 votes
-----------------
 userid   gameid
-------  --------
   a        1
   a        2
   a        3
   b        1
   b        2

and a games table:

 games
----------------
 gameid   title
 ------   ------
    1       foo
    2       bar
    3       fizz
    4       buzz

What kind of a join would I use to perform the query "Select * from games where [user A voted on the game]"?

I've tried following Jeff's guide, but I'm not getting the expected results.

score 6 · Accepted Answer · answered Aug 11 '11 at 22:37

6

You would use an INNER join to establish the relationship between the common gameid field;

select
  votes.userid,
  games.title
from games
  inner join votes on (votes.gameid = game.gameid)
where
  votes.userid = 'a'

answered Aug 11 '11 at 22:37

Alex K.

171,639
30
264
288

Perfect! Thanks. I was definitely doing my "on" clause wrong, which I think was my first problem. Inner Join makes sense, because I don't want any records that don't match in both. – xdumaine Aug 11 '11 at 22:43

score 1 · Answer 2 · answered Aug 11 '11 at 22:38

1

This gets you the games with a vote:

SELECT
   g.title
   v.userid
FROM
   games g

   INNER JOIN votes v
   ON g.gameid = v.gameid

This gets you all of the games, even if not voted on:

SELECT
   g.title
   v.userid
FROM
   games g

   LEFT JOIN votes v
   ON g.gameid = v.gameid

answered Aug 11 '11 at 22:38

Chains

12,541
8
45
62

Good answer, given the ambiguous question. – Karl Aug 11 '11 at 23:07

onedaywhen · Answer 3 · 2011-08-17T15:17:37.410

The relational operator you require is semijoin.

Most SQL products lacks an explicit semijoin operator or keyword. Standard SQL-92 has a MATCH (subquery) predicate but is not widely implemented (the truly relational language Tutorial D uses the keyword the MATCHING for its semijoin operator).

A semijoin can of course be written using other SQL predicates. The most commonly seen use EXISTS or IN (subquery).

Depending on the data it may be possible to use SELECT DISTINCT..INNER JOIN. However, in your case you are using SELECT * FROM ... and an INNER JOIN will project over the votes table resulting in userid being appended to the column list along with a duplicate column for gameid (if your SQL product of choice supports it, using NATURAL JOIN would solve the duplicate column problem and mean you would omit the ON clause too).

Using INTERSECT is another possible approach if your SQL product supports it and again depending on the data (specifically, when the headings of the two tables are the same)>

Personally, I prefer to use EXISTS in SQL for semijoin because the join clauses are closer together in the written code and doesn't result in projection over the joined table e.g.

SELECT *
  FROM games
 WHERE EXISTS (
               SELECT * 
                 FROM votes AS v
                WHERE v.gameid = games.gameid
                      AND v.userid = 'a'
              );

This is interesting. It achieves the same thing as the Inner Join, but without returning the values from the votes table. Is there any significant performance difference? — xdumaine, Aug 13 '11 at 02:44

score 0 · Answer 4 · answered Aug 11 '11 at 22:45

0

Seems like, given your limited description, the following would get you a list of games where user A voted on the game:

select g.gameid, g.title
from games g
inner join votes v
on v.gameid = g.gameid
where v.userid = 'a'

answered Aug 11 '11 at 22:45

Chris

1,401
4
17
28

What kind of join do I need?

4 Answers4

Linked