views:

27

answers:

2

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 
+2  A: 

INNER JOIN returns non NULL matches, whereas LEFT JOIN can be NULL on one side. I think this is the clue.

Tomasz Kowalczyk
+3  A: 

The expression (gameslists.id is NULL) can never be true in the INNER JOIN query (assuming id is the primary key). That's why the first result set contains no rows.

On the other hand, whenever the ON clause of the LEFT JOIN does not match, the gameslists fields will be NULL for that particular row. Therefore your second query will return all the games that do not appear in gameslists, unless memberid is 351.

Daniel Vassallo
Thanks Daniel!!!
Mel
@Mel: What is producing an error?
Daniel Vassallo
Not, an error, but also nulls: LEFT JOIN gameslists ON gameslists.gameid = games.idWHERE gameslists.id is NULL AND gameslists.memberid <> 352
Mel
@Mel: What is the intended result? You want all games that do not appear in gameslists? And what about memberid?
Daniel Vassallo
The intended result is achieved in my second query (that returns 462 results; I want to select all games that do not exist in gameslists given a certain memberid); I would like to know if I can achieve the same thing by moving the "AND gameslists.memberid = ???" clause from the JOIN part to the WHERE part of the query. Why? They framework I'm using will not allow me to have two arguments in the JOIN clause (it will join gameslists.gameid = games.id, but I can't have the "AND gameslists.memberid" clause at the same time.
Mel
@Mel: I'll update my answer with an example in a few minutes...
Daniel Vassallo
@Mel: What I can't understand is the `memberid` field. Because you're selecting all `games` that do not exist in `gameslist`, and `memberid` is a field in `gameslist`. Therefore by definition, all `games` that do not have an entry in `gameslist` will not have a `memberid` field.
Daniel Vassallo
The reason for memberid is this: Say a member has added two games. I want to eliminate those two games for him only, hence the condition needs to be double not (gameslists.gameid = games.id AND gamelists.memberid <> session.member.id)... if I don't have memberid as a filter, and all the games are added to lists, no one will be able to see anything any more!
Mel