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

Depends on where the filter is applied in the JOIN condition or the WHERE clause. — user2989408, Jan 13 '14 at 17:37
@user2989408 the filter is applied in the JOIN condition as shown in the example. — user624558, Jan 13 '14 at 17:39

score 11 · Answer 1 · answered Jan 13 '14 at 17:44

11

Should be the same as:

SELECT s.id FROM Student s 
LEFT OUTER JOIN (Select * from Student_Instructor where name='John' ) i
ON s.student_id=i.student_id

answered Jan 13 '14 at 17:44

suf.agent

136
3

Also, can provide some examples if needed. – suf.agent Jan 13 '14 at 17:45
1

This is the correct answer: the static join condition on the right side table is a filter on the table prior to the join. I tested this on sample data to be sure. Adam's point is correct, but now that `i.name` was added to the select in the example this answers the question of how join conditions alter the right side of the join. – Merlin Oct 10 '20 at 10:35

score 3 · Answer 2 · answered Jan 13 '14 at 17:44

In your example query, only rows where i.name = 'John' would be returned. I think you would want to also include or i.name is null to include all rows where a Student record does include a student Instructor.

SELECT s.id FROM Student s , isnull(i.name, 'No Student Insturctor') as [Student Instructor]
LEFT OUTER JOIN Student_Instructor i
ON s.student_id=i.student_id
AND i.name='John' or i.name is null

score 1 · Accepted Answer · answered Jan 13 '14 at 18:01

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.

score 0 · Answer 4 · answered Mar 29 '17 at 20:59

This can be done using Oracle (+) notation as well-

SELECT s.id FROM Student s, Student_Instructor i
WHERE s.student_id = i.student_id (+)
AND i.name(+)='John'

Although, Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator.

Adding filter on the right side table on Left outer joins

4 Answers4