How To Write Outer Joins And Nested Loop Join In Oracle

The Oracle right outer join looks almost the same as the left join only in this case the outer-condition-sign “(+)” is applied on left table “my_a“. In right outer join the master table is the right joined table and the slave table is on the left side. This means the Oracle Select will return all rows from the right joined table “my_b” and only with the condition matching lines from the left joined table “my_a”. The following example will show 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 version of the Oracle left join will change also the LEFT keyword with the RIGHT and the OUTER JOIN and ON keywords are remaining in the condition as they were a part of the query of the outer left join. Take a look at the following ANSI syntax of right outer join:

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

The next ANSI Oracle right outer join example query will return all lines from right side “my_b” of join and only rows from left side “my_a” that has a match.

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 third outer joins type is Oracle full join. This means we will show all lines in both tables and the join will match them where it is possible by the condition. The full outer join type has only an ANSI standard condition and it is as the following syntax:

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

The next example shows how to write the full outer join and you can find in the output cells with “null” value that has no match on another side of ID-s, but they are still included to the output.

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 nested loop join is a type of join where all matching values returned by a sub-query will be included to the master query. The sub-query can be found after the IN keyword and inside the brackets. The following syntax shows the common structure of a nested loop join:

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

The following example shows how to write a nested loop join with the master query “my_a” and the sub-query “my_b” is inside the brackets :

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);

From the output above you can find that table “my_a” returned only 7,8,9,10 because they are the only matching values in the sub-query. The sub-query is the lowest one.

See Also:

Previous Page_

_Next Page