{question}
How should I filter a table that is joined by an outer join (RIGHT/LEFT)?
{question}
{answer}
When filtering on a table joined by an outer join, it is important to distinguish between filters done in the WHERE clause to ones in the ON clause.
See the following example:
create table a (id int); create table b (id int); create table c (id int); insert into a select * from (select 1 union select 3); insert into b select * from (select 1 union select 3); insert into c select * from (select 3 union select 4); -- Query 1 - no filter select a.id a_id, b.id b_id, c.id c_id from a inner join b on a.id = b.id right join c on a.id = c.id; +------+------+------+ | a_id | b_id | c_id | +------+------+------+ | 3 | 3 | 3 | | NULL | NULL | 4 | +------+------+------+ -- Query 2 - filter in the WHERE select a.id a_id, b.id b_id, c.id c_id from a inner join b on a.id = b.id right join c on a.id = c.id where b.id = 4; Empty set -- Query 3 - filter in the ON select a.id a_id, b.id b_id, c.id c_id from a inner join b on a.id = b.id and b.id = 4 right join c on a.id = c.id; +------+------+------+ | a_id | b_id | c_id | +------+------+------+ | NULL | NULL | 3 | | NULL | NULL | 4 | +------+------+------+
It is important to know that all filtering done on the outer table will result in the join turning into an inner join.
{answer}