ORACLE SQL OUTER JOIN - Online Tech Support

How To Write Oracle Inner and Outer Join – Part 2

online tech support Comments Off

This Online Tech Support tutorial page is based on examples. This is the second part of our join examples and the following join type is the Oracle Inner Join. The join returns only rows that has match in both joined tables. The Oracle inner join is also called as a simplified join. The first example shows how the simplified join returns only matching lines and who has ID equal with “7“:

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a, (
 SELECT rownum + 6 AS Id
   FROM DUAL
CONNECT BY rownum < 11) b
WHERE a.id = b.id
  AND a.id = 7;

The second query is written in the ANSI standard way and the inner join is added with the condition with the equal sign (=) after the “ON” keyword. All additional conditions can be written after the “ON” keyword condition or you can simply add the WHERE and the conditions. The ANSI standard syntax is:

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

Now the example is as the first one only in the ANSI standard. The inner join has the same ID is “7” condition and the output looks the same as on the first example – only one row with two 7 values.

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a JOIN
 (
 SELECT rownum + 6 AS Id
   FROM DUAL
CONNECT BY rownum < 11) b ON a.id = b.id AND a.id = 7;

The second join type is Oracle outer join and it does return all lines from the master table or in case of full outer join all rows from both tables. The join will still try to connect rows where is possible.

There are 3 types of Oracle outer joins: the left outer join, the right outer join and the full outer join. The outer join operator in Oracle is “(+)” and the condition will be applied depending on which side the operator is added. The side where the operator is applied we will call it as a “slave” table and another side as a “master“. The operator will always show all lines form the master table side.

The following example is Oracle left outer join or it is called also as the Oracle left join. The operator will return all rows from the left side query with alias “a“. The condition will try to join the rows where is possible on the right side query with alias “b“. In this example we will name “master” query alias “a” and the “slave” is alias “b” that also has next to it the outer join operator.

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a, (
 SELECT rownum + 6 AS Id
   FROM DUAL
CONNECT BY rownum < 11) b
WHERE a.id = b.id(+);

Take a look at the output above it has all values from query “a” and only four values (ID: 7,8,9,10) from query “b“.

The same left join in Oracle ANSI standard. In the ANSI standard the left outer join is defined by keyword “LEFT OUTER JOIN” as it is done on the following syntax:

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

The third example with ANSI standard and the left outer join will return the same rows as on the example above and again the “a” query is master and the “b” is slave.

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a LEFT OUTER JOIN
(
 SELECT rownum + 6 AS Id
   FROM DUAL
CONNECT BY rownum < 11) b ON a.id = b.id;

The output shows the same values as it was in the “Oracle style” and as the result is the same it depends more on users how do they like to write the queries or which style suits more on them.

See Also:
Oracle Select

Previous Part

Home

Next Part

How To Write Oracle JOIN – Part 3

online tech support Comments Off

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