How To Write Inner Join or Left Outer Join In Oracle

This tutorial page is based on examples to make it easier to follow. If you red the last page then now we got a fourth Join type. The fourth oracle join is oracle inner join. This Oracle join returns only rows where both table joined column values are the same. In most cases the Oracle inner join is called as a simplified join. As the following example:

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
  AND a_id = 7;

Now we will continue with the ANSI type of inner join, so for the ANSI standard inner join you need to add the equals sign condition after keyword ON and additional conditions after that as the syntax shows:

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

As the syntax above showed the basic structure now we will write the real ANSI inner join. Take a look at the following one and try to find out the main keywords from the syntax sample.

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 JOIN my_b
 ON a_id = b_id AND a_id = 7;

The query returned only matching records that were IDs 7 and ignore rest of it. To see more than matching ones you would need to use Oracle outer join.

The Oracle outer join purpose is to return all lines form a table or tables and trying to join them up where is possible, so all lines from outer joined table will be returned mo matter if it had any match or not. There are 3 types of outer joins in oracle: a left outer join, a right outer join and full outer join. The outer join operator is “(+)” and which side it is added that will so called slave table and will be joined with the master table. We are going to show always all lines form the master table.

The next example will be with Oracle left outer join or Oracle left join. The purpose is to show all lines of the left condition table and join and show only joined lines if possible from the right table.

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

As you see from the result above the my_a table with column name A_ID has returned all lines and table my_b with column name B_ID has available only lines that has match with table my_a.

Now the ANSI standard the same left outer join in Oracle or the left join Oracle. The ANSI standard defines the left outer join as the following syntax and it is using keyword “LEFT OUTER JOIN” to define the join type:

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

The next example is in ANSI standard left 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 LEFT OUTER JOIN my_b
 ON a_id = b_id;

When you do compare the last query result with the one before you will see the oracle left join of ANSI returns exactly the same lines as the Oracle outer join. In short, they look different but work in the same way.

See Also:

Previous Page_

_Next Page