What type of mysql join do I need in the following query

Question

I have the following tables:

platforms(id,platformname)
games(id,title,platformid)
gameslists(id,memberid,gameid)

I would like to select all records from the games table, but exclude records where games.gameid is present in gameslists for a specific member. So in plain English: select all records from the games table except for those where the gameid is present in gameslists table where the memberid equals 999 (memberid will come from the session). I also need the platform name, but I think that's a simple inner join.

I tried this, and other variations but to no avail!

SELECT DISTINCT games.id, platforms.platformname, gameslists.gameid
FROM games
INNER JOIN platforms ON games.platformid = platforms.id
INNER JOIN gameslists ON games.id = gameslists.gameid
WHERE platformid = 1 and gameslists.memberid <> 999
ORDER BY games.releasedate DESC
LIMIT 8

Answer 1

Using LEFT JOIN/IS NULL

   SELECT g.*
     FROM GAMES g
LEFT JOIN GAMESLISTS gl ON gl.gameid  = g.id
                       AND gl.memberid = ?
    WHERE gl.id IS NULL

Using NOT IN

SELECT g.*
  FROM GAMES g
 WHERE g.id NOT IN (SELECT gl.gameid 
                      FROM GAMESLISTS gl
                     WHERE gl.memberid = ?)

Using NOT EXISTS

SELECT g.*
  FROM GAMES g
 WHERE NOT EXISTS(SELECT NULL
                    FROM GAMESLISTS gl
                   WHERE gl.gameid = g.id
                     AND gl.memberid = ?)

Summary

In MySQL, the LEFT JOIN/IS NULL is the most efficient means of getting a list based on what to exclude, but only if the columns being compared are not nullable (the values in the join criteria can't be null). Otherwise, the NOT IN and NOT EXISTS are more efficient.

Conclusion

Because of the foreign keys, it's unlikely the columns compared will be NULL so use the LEFT JOIN/IS NULL. But be aware that you don't need to use JOINs specifically for excluding data.

Answer 2

Check out this guide from Stack Overflow's very own Jeff Atwood