Online Tech Support

The Right Outer Join looks almost the same as the Oracle Left Outer Join (Part 2) only that the outer condition sign is set on the left table side. On this condition the master table is the right joined table and the slave table is on the left side. That means the select statement will return all lines from the right joined table “MY_B” and only the matching lines from the left joined table “MY_A“. The following example will demonstrate the Oracle right outer join:

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a, my_b
WHERE a_id(+) = b_id;

The ANSI standard the Oracle Right Outer Join will change also keywords the LEFT with RIGHT as the following syntax is showing:

SELECT <columns> 
  FROM <table1> 
    RIGHT OUTER JOIN <table2> ON <conditions>;

The next Oracle right outer join will return all lines from the right side joined table “MY_B” and only matching lines from the left joined table “MY_A“.

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a RIGHT OUTER JOIN my_b
 ON a_id = b_id;

The next outer joins type is Oracle Full Outer Join. It means we will show all lines in both tables and the join will match them where it’s possible by added condition. Oracle has full outer join condition only with the ANSI standard style. The syntax is following:

SELECT <columns> 
  FROM <table1> 
   FULL OUTER JOIN <table2> ON <conditions>;

This Oracle Full Outer Join example has match for IDs 7,8,9 and 10. The other lines have no match so you can find next to them a NULL column.

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a FULL OUTER JOIN my_b
 ON a_id = b_id;

The another join type is the Oracle Nested Loop Join. This Oracle join will return all matching values that did exists in the slave query. There is an exception about the slave query that there SHOULD NOT be any NULL values or the join returns no results. The Oracle Nested Loop Join syntax is:

SELECT <columns> 
  FROM <table1> 
 WHERE <columns> IN (SELECT <columns> FROM <table2>);

The following Nested Loop Join will have the master table “MY_A” and the slave table “MY_B“. All ID values existing in table “MY_B” will try to be matching the “IN” keyword.

WITH my_a AS (
 SELECT rownum AS a_Id
 FROM DUAL
CONNECT BY rownum < 11),
my_b AS(
 SELECT rownum + 6 AS b_Id
 FROM DUAL
CONNECT BY rownum < 11)
 
SELECT * FROM my_a
WHERE a_id IN (SELECT b_id FROM my_b);

Table “MY_A” has returned only 7,8,9,10 because they are only matching values in table “MY_B” that is sitting in the sub-query after the “IN” keyword.

See Also:

Previous Part

Home

Next Part