How To Write Outer Joins And Nested Loop Join In Oracle

How To Write Outer Joins And Nested Loop Join In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle nested loop join oracle nested loop join in oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle nested loop join oracle nested loop join in oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle nested loop join oracle nested loop join in oracle outer join full outer join oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

dba oracle dba database management system oracle performance tuning oracle join oracle join in oracle outer join oracle outer join in oracle web design courses learn web design oracle semijoin oracle semijoin in oracle antijoin oracle antijoin in oracle nested loop join oracle nested loop join in oracle outer join full outer join oracle personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*