Adding filter on the right side table on Left outer joins

sql sql-server join left-join right-join
user624558 picture user624558 · Jan 13, 2014 · Viewed 10.4k times · Source

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

suf.agent picture suf.agent · Jan 13, 2014

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

Related questions

Difference between left join and right join in SQL Server

I know about joins in SQL Server. For example. There are two tables Table1, Table2. Their table structures are the following. create table Table1 (id int, Name varchar (10)) create table Table2 (id int, Name varchar (10)) Table1 data as follows: Id …

Read More
Join statement order of operation

Given the following 3 way join select t1.* from t1 left join t2 on t1.fk = t2.pk join t3 on t2.fk = t3.pk If the join between t2 and t3 failed, would the row from the successful join between …

Read More
left join two tables on a non-unique column in right table

I have two tables in sql server and i wanna select and join some data from these table.the first tables have some customer like: --------------- customer id Dave 1 Tom 2 --------------- and second table i table of purchases that includes …

Read More