Oracle ANSI SQL Syntax
Oracle Tables A and B
The Oracle ANSI SQL Join syntax is shown below.
1. INNER JOIN: Table A and Table B on Idx
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,  ta.idx_desc
FROM table_a ta INNER JOIN table_b tb
  ON  ta.idx = tb.idx;

Oracle Tables A Inner Join Table B

Oracle ANSI Inner Join Query Output


2. JOIN ON: Table A JOIN Table B ON Idx
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,  ta.idx_desc
FROM table_a ta JOIN table_b tb  ON (ta.idx = tb.idx);

Oracle ANSI Join On Query Output
3. JOIN USING: Table A JOIN Table B USING (Idx)
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 * FROM table_a ta JOIN table_b tb USING (idx);

Oracle ANSI Join Using Query Output

Oracle ANSI OUTER Join

Archive Reference Oracle ANSI SQL Syntax

Oracle registered trademark of Oracle Corporation.

Last Revised On: January 14th, 2015

  38239