Filter on an outer join

{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}