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.
;
Leave a Reply