SQL WHERE IN with Left Join is not returning all rows I expect

Question

I'm building a little conjugation/radicalization app, and I have stumbled upon a problem. I have this SQL request:

SELECT    DISTINCT RA.* 
FROM      radical RA 
left join conjugated CO 
on        CO.word_id = RA.id 
where     CO.conjugation IN ('I', 'am', 'a', 'cat')

That returns:

| id | radical |
| 13 |  to be  |

However, I would like to get a result of the type:

| id   | radical | word |
| null |  null   |  I   |
|  13  | to be   | am   |
| null |  null   |  a   |
| null |  null   | cat  |

Does anyone know how?

Please post your sample data – Oto Shavadze Mar 28 '17 at 15:43 — Oto Shavadze, Mar 28 '17 at 15:43

score 1 · Answer 1 · answered Mar 28 '17 at 15:46

You need a left join, but to start with all the words you want to keep:

select w.word, ra.* 
from (select 'I' as word union all
      select 'am' union all select 'a' union all select 'cat'
     ) w left join
     conjugated co
     on co.conjugation = w.word left join
     radical ra
     on ra.id = co.word_id;

If these values are in conjugation, you can simply do:

select c.onjugation, ra.* 
from conjugated co left join
     radical ra
     on ra.id = co.word_id
where c.conjugation in ('I', 'am', 'a', 'cat') ;

That is, conjugation should be first, because you want to keep all matching rows in that table.

score 0 · Accepted Answer · answered Mar 28 '17 at 15:59

Seemingly you are using a Left Join, when you actually need a Right join (since it appears you want all rows of the right table matching the predicate to be returned)

So either switch the join:

SELECT    DISTINCT RA.*, co.`conjugated` as word
FROM      radical RA 
right join conjugated CO 
on        CO.word_id = RA.id 
where     CO.conjugation IN ('I', 'am', 'a', 'cat');

Or switch the order of the tables in the FROM:

SELECT    DISTINCT RA.*, co.`conjugated` as word
FROM      conjugated CO 
left join radical RA 
on        CO.word_id = RA.id 
where     CO.conjugation IN ('I', 'am', 'a', 'cat');

SQL WHERE IN with Left Join is not returning all rows I expect

2 Answers2