Adding filter on the right side table on Left outer joins

Question

On outer joins(lets take a left outer join in this case) how does adding a filter on the right side table work?

SELECT s.id, i.name FROM Student s 
LEFT OUTER JOIN Student_Instructor i
ON s.student_id=i.student_id
AND i.name='John'

I understand that if the filter was on the Student table it would be more like "Get all rows with name= John first and join the tables".

But I am not sure if that is the case if the filter is on the right side table(Student_Instructor). How does the filter i.name='John' gets interpreted?

Thank you

Answer 1

All rows will be returned from your left table regardless. In the case of a left join, if the filter isn't met, all data returned from the right table will show up as null. In your case, all students will show up in your results. If the student doesn't have an instructor, i.name will be null.

Since you are only selecting a column from your left table, your join is pretty useless. I would also add i.name to your select, so you can see the results

In the case of an inner join, rows will only be returned if the join filter is met.