Menu
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
781
When doing a left join in SQL any filtering of the table after the join will turn it into an inner join. However there are some easy ways to do the filtering first. Suppose you've got some tables related to a website. The pages table describes the different pages on the site.
A join filter allows a table to be filtered based on how a related table in the publication is filtered. Typically a parent table is filtered using a parameterized filter; then one or more join filters are defined in much the same way that you define a join between tables.
Left outer join includes the unmatched rows from the table which is on the left of the join clause whereas a Right outer join includes the unmatched rows from the table which is on the right of the join clause.
Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It's possible, though that you might want to filter one or both of the tables before joining them. For example, you only want to create matches between the tables under certain circumstances.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
