Andrew Fraser DBA

Outer Join (+) and ANSI Syntax

These two are functionally identical:

1) Old style (+) syntax:

WITH a ( id , ca ) AS ( SELECT 1 , '1' FROM DUAL UNION ALL SELECT 2 , '2' FROM DUAL )
   , b ( id , cb ) AS ( SELECT 1 , '1' FROM DUAL UNION ALL SELECT 3 , '3' FROM DUAL )
SELECT a.ca , b.cb
  FROM a , b
 WHERE a.id = b.id(+)  -- both a returned, only one b.
;

2) Newer ANSI join syntax:

WITH a ( id , ca ) AS ( SELECT 1 , '1' FROM DUAL UNION ALL SELECT 2 , '2' FROM DUAL )
   , b ( id , cb ) AS ( SELECT 1 , '1' FROM DUAL UNION ALL SELECT 3 , '3' FROM DUAL )
SELECT a.ca , b.cb
  FROM a LEFT OUTER JOIN b ON a.id = b.id  -- both a returned, only one b.
;