SQL join return rows when Where not satisfied

Question

I have a table of Monkeys and a table of Trees with a many-to-many relationship joined by a third MonkeysTrees FK Table.

I have this query that returns all the Monkeys and Trees for a given Tree Id:

SELECT * 
FROM Monkeys m
JOIN MonkeysTrees mt ON mt.MonkeyId = m.Id
join Trees t ON t.Id=mt.TreeId
WHERE t.Id = 1;

If I change t.Id in the final WHERE clause to an Id that isn't in the Trees table, I'd still like to return a table containing all the Monkeys, but with NULL in the joined fields. How could I do that?

Answer 1

You can use an outer join, the position of the on clauses below yields Monkeys left join (MonkeysTrees inner join Trees where treeid = 1)

SELECT * 
FROM Monkeys m
LEFT JOIN MonkeysTrees mt
           INNER JOIN Trees t ON t.Id=mt.TreeId AND t.Id = 1
ON mt.MonkeyId = m.Id