How To Write Oracle JOIN - Part 3
  Home
 

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;

online tech support online computer help computer technician computer problems oracle sql database programming database oracle retirement planning retirement sql database oracle database sql outer join oracle outer join in oracle outer join oracle sql outer join in oracle sql learn sql oracle full outer join oracle

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;

online tech support online computer help computer technician computer problems oracle sql database programming database oracle retirement planning retirement sql database oracle database sql outer join oracle outer join in oracle outer join oracle sql outer join in oracle sql learn sql oracle full outer join oracle

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;

online tech support online computer help computer technician computer problems oracle sql database programming database oracle retirement planning retirement sql database oracle database sql outer join oracle outer join in oracle outer join oracle sql outer join in oracle sql learn sql oracle full outer join oracle

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

online tech support online computer help computer technician computer problems oracle sql database programming database oracle retirement planning retirement sql database oracle database sql outer join oracle outer join in oracle outer join oracle sql outer join in oracle sql learn sql oracle full outer join oracle

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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*