How To Write Inner Join or Left Outer Join In Oracle

How To Write Inner Join or Left Outer Join In Oracle

February 29, 2020
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

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;

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 inner join oracle inner 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

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;

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

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 inner join oracle inner join in oracle oracle left 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

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;

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 inner join oracle inner join in oracle oracle left 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

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

Leave a Reply

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

*