Right Join and Left Join acting different

[A Williams]

I have two queries, one using a right join and one using a left join that are producing different results, but they should be the same. The RIGHT join is acting like an inner join.

Please see the queries below . . . In the first one I have the workorder (w) table on the RIGHT side of the ON statement and a RIGHT join. In the second one, I have the the workorder (w) table on the LEFT side of the ON statement and a LEFT join, so these two queries should return the same results, but they aren't.

What am I missing??


--returns 53198 records without a where clause, 394 with the where clause
select w.wonum,w.siteid,w.orgid, mostrecentworklog.logtype,mostrecentworklog.descri ption,
mostrecentworklog.siteid,mostrecentworklog.orgid,m ostrecentworklog.recordkey,
mostrecentworklog.ldtext,mostrecentworklog.createb y,mostrecentworklog.createdate from workorder w
right join (select logtype,description,siteid,orgid,recordkey,ld.ldte xt,createby,createdate from worklog
left join longdescription ld on ld.ldownertable='worklog' and worklog.worklogid=ld.ldkey
where class='WORKORDER'
and worklogid in (select max(worklogid) from worklog group by siteid,class, recordkey)
) as MostRecentWorkLog
on MostRecentWorkLog.recordkey=w.wonum and MostRecentWorkLog.siteid=w.siteid and MostRecentWorkLog.orgid=w.orgid
where w.siteid='ALE' and w.reportdate>'2012-01-01'
order by w.wonum

--returns 4138193 records without a where clause, 4267 with the where clause
select w.wonum,w.siteid,w.orgid, mostrecentworklog.logtype,mostrecentworklog.descri ption,
mostrecentworklog.siteid,mostrecentworklog.orgid,m ostrecentworklog.recordkey,
mostrecentworklog.ldtext,mostrecentworklog.createb y,mostrecentworklog.createdate from workorder w
left outer join (select logtype,description,siteid,orgid,recordkey,ld.ldte xt,createby,createdate from worklog
left join longdescription ld on ld.ldownertable='worklog' and worklog.worklogid=ld.ldkey
where class='WORKORDER'
and worklogid in (select max(worklogid) from worklog group by siteid,class, recordkey)
) as MostRecentWorkLog
on w.wonum=MostRecentWorkLog.recordkey and w.siteid=MostRecentWorkLog.siteid and w.orgid=MostRecentWorkLog.orgid
where w.siteid='ALE' and w.reportdate>'2012-01-01'
order by w.wonum

[Rabbit]

Your joined sources are in the same place. You seem to think that it is the ON part of the join statement that dictates which one is left and which one is right. But that is not the case.

FROM x LEFT JOIN y
FROM y RIGHT JOIN x

These are equivalent.

FROM x LEFT JOIN y
FROM x RIGHT JOIN y

These are not equivalent.

[ck9663]

Remember this: A LEFT join with a where clause using a column on the right table or a RIGHT join with a where clause using a column on the left table is actually an INNER JOIN...

Happy Coding!!!

~~ CK