tag:blogger.com,1999:blog-518481768015386858.post6863786008728595797..comments2023-08-06T04:28:52.092-04:00Comments on Karen Morton: Left outer join equivalence - Oracle vs ANSI syntaxKarenhttp://www.blogger.com/profile/03309823327597536648noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-518481768015386858.post-24516858399383907912010-10-28T10:38:19.714-04:002010-10-28T10:38:19.714-04:00@sablime, Adding the 'd.deptno is null' pr...@sablime, Adding the 'd.deptno is null' predicate doesn't provide the same result set as the correctly used outer join filter (d.deptno(+) = 20). If you use the predicate you suggest, 6 rows are returned. The 5 in deptno = 20 and the one row for employee KING whose deptno is null. We actually want our answer to return all rows in the emp table and include the department info for those employees in deptno 20. Simply adding the 'deptno is null' filter to the predicate won't produce that answer.<br /><br />This approach was worth a shot though and supports another reason why it's important to always keep in mind the question you want the query to answer.Karenhttps://www.blogger.com/profile/03309823327597536648noreply@blogger.comtag:blogger.com,1999:blog-518481768015386858.post-29976587991676357072010-10-26T15:33:09.557-04:002010-10-26T15:33:09.557-04:00the clause where d.deptno = 20 changes the join to...the clause <b>where d.deptno = 20</b> changes the join to a regular join; you want <b>where (d.deptno is null or d.deptno = 20)</b>Unknownhttps://www.blogger.com/profile/12853232752092572901noreply@blogger.com