Oracle ANSI SQL Outer Join Syntax
Oracle Tables A and B
The Oracle ANSI SQL Outer Join syntax is shown below.
Table A LEFT OUTER JOIN Table B
WITH table_a AS
(SELECT 10 idx, 'idx_10' idx_desc FROM DUAL UNION
 SELECT 15 idx, 'idx_15' idx_desc FROM DUAL UNION
 SELECT 20 idx, 'idx_20' idx_desc FROM DUAL UNION
 SELECT 25 idx, 'idx_25' idx_desc FROM DUAL UNION
 SELECT 30 idx, 'idx_30' idx_desc FROM DUAL),
table_b AS
(SELECT 10 idx, 'idx_10' idx_desc FROM DUAL UNION
 SELECT 15 idx, 'idx_15' idx_desc FROM DUAL UNION
 SELECT 20 idx, 'idx_20' idx_desc FROM DUAL UNION
 SELECT 35 idx, 'idx_35' idx_desc FROM DUAL UNION
 SELECT 40 idx, 'idx_40' idx_desc FROM DUAL)
SELECT  ta.idx idx_a,  ta.idx_desc desc_a,
 tb.idx idx_b,  tb.idx_desc desc_b
FROM table_a ta LEFT OUTER JOIN table_b tb 
ON   ta.idx = tb.idx;

Oracle Table A LEFT OUTER JOIN Table B

Oracle ANSI Left Outer Join Query Output
Table A RIGHT OUTER JOIN Table B
WITH table_a AS
(SELECT 10 idx, 'idx_10' idx_desc FROM DUAL UNION 
 SELECT 15 idx, 'idx_15' idx_desc FROM DUAL UNION
 SELECT 20 idx, 'idx_20' idx_desc FROM DUAL UNION
 SELECT 25 idx, 'idx_25' idx_desc FROM DUAL UNION
 SELECT 30 idx, 'idx_30' idx_desc FROM DUAL),
table_b AS
(SELECT 10 idx, 'idx_10' idx_desc FROM DUAL UNION
 SELECT 15 idx, 'idx_15' idx_desc FROM DUAL UNION
 SELECT 20 idx, 'idx_20' idx_desc FROM DUAL UNION
 SELECT 35 idx, 'idx_35' idx_desc FROM DUAL UNION
 SELECT 40 idx, 'idx_40' idx_desc FROM DUAL)
SELECT  ta.idx idx_a,  ta.idx_desc desc_a,
 tb.idx idx_b,  tb.idx_desc desc_b
FROM table_a ta RIGHT OUTER JOIN table_b tb 
ON   ta.idx = tb.idx;

Oracle Table A RIGHT OUTER JOIN Table B

Oracle ANSI Right Outer Join Query Output
Table A FULL OUTER JOIN Table B
WITH table_a AS
(SELECT 10 idx, 'idx_10' idx_desc FROM DUAL UNION
 SELECT 15 idx, 'idx_15' idx_desc FROM DUAL UNION
 SELECT 20 idx, 'idx_20' idx_desc FROM DUAL UNION
 SELECT 25 idx, 'idx_25' idx_desc FROM DUAL UNION
 SELECT 30 idx, 'idx_30' idx_desc FROM DUAL),
table_b AS
(SELECT 10 idx, 'idx_10' idx_desc FROM DUAL UNION
 SELECT 15 idx, 'idx_15' idx_desc FROM DUAL UNION
 SELECT 20 idx, 'idx_20' idx_desc FROM DUAL UNION
 SELECT 35 idx, 'idx_35' idx_desc FROM DUAL UNION
 SELECT 40 idx, 'idx_40' idx_desc FROM DUAL)
SELECT  ta.idx idx_a,  ta.idx_desc desc_a,
 tb.idx idx_b,  tb.idx_desc desc_b
FROM table_a ta FULL OUTER JOIN table_b tb 
ON   ta.idx = tb.idx;

Oracle Table A RIGHT OUTER JOIN Table B

Oracle ANSI Full Outer Join Query Output

Oracle ANSI Join


Archive Reference Oracle ANSI SQL Syntax

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 14th, 2015

  251