Assuming the following MySQL table structure, why do the two following queries produce different results?
games(id) (464 records)
members(id) (1 record, id=351)
gameslists(id,memberid,gameid) -- (2 records, (1,351,1) and (2,351,2))
This produces null
SELECT games.*
FROM games
INNER JOIN gameslists ON gameslists.gameid = games.id
WHERE gameslists.memberid <> 351 AND gameslists.id is NULL
This produces 462 records, which is what I expect.
SELECT games.*
FROM games
LEFT JOIN gameslists ON gameslists.gameid = games.id AND gameslists.memberid <> 351
WHERE gameslists.id is NULL