1

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.

4
  • Just curious .. in your first you have '5' and in your second you have 5 .. is this simply a typo? – Joishi Bodio Jun 24 '15 at 21:28
  • 1
    Did you mean to leave out the user_id IS NULL condition on your second query? – Chris Jun 24 '15 at 21:42
  • You probably want to use SELECT * FROM card LEFT OUTER JOIN user_card ON card.id = user_card.card_id AND user_card.user_id = 5 WHERE card.package_id =14 which is equivalent to the 2nd query. – ypercubeᵀᴹ Jun 24 '15 at 21:56
  • @Chris user_id in table user_card cannot be NULL on its own, it only become NULL in the resulting joined table when there is no corresponding card_id in the user_card (right table) to the id in card table (left table) – RRostami Jun 25 '15 at 7:09
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.