Mysql – Why conditioning before and after join, gets different results

join;MySQLsubquery

I want to left join two tables and then return some of the records.

My first query was:

SELECT * 
FROM card
LEFT OUTER JOIN user_card ON card.id = user_card.card_id
WHERE card.package_id =14
AND ( user_card.user_id =  '5'
OR user_card.user_id IS NULL)

Then when I noticed some problems
(ie result has fewer rows than just SELECT FROM card WHERE package_id=14)

I wrote this:

SELECT * 
FROM (select * from card WHERE package_id=14 ) as cc
LEFT OUTER JOIN (select * from user_card WHERE user_id=5 ) as uu ON cc.id = uu.card_id

Now:

  1. Why these two queries act differently?

  2. How can I do it right without sub-querying ?

P.S : I'm using MySQL, and all id columns are INT.

Best Answer

To answer your first question:

In the first query, some records are filtered out AFTER getting the result of the left join. These rows are the ones that meet this condition: card.package_id =14 AND user_card.user_id <> 5 [AND user_card.user_id IS NOT NULL]

On the other hand, in the second query, ALL result rows with "card.package_id =14" are returned regardless of the value of user_card.user_id

Example:

card.id     card.package_id
11          14
12          14
13          55

user_card.card_id   user_card.user_id 
11                  5
12                  6

All possible values:

card.id package_id user_card.user_id
11      14         5
11      14         6
12      14         5
12      14         6
13      55         5
13      55         6

The first query will filter out the the rows with user_id=6, although the value of package_id is 14, (and will filter out all reos with package ID <> 14), so the returned values will be:

card.id package_id user_card.user_id
11      14         5
12      14         5

The second query would keep all rows with package id = 14, and give null to user id:

card.id package_id user_card.user_id
11      14         5
11      14         NULL
12      14         5
12      14         NULL

You can judge which output is needed, and based on that you chose which query to use.

Related Question