How To Write Oracle INNER and OUTER Joins - Part 2
  Home
 

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;

online tech support online computer help computer technician computer problems computer outer join oracle outer join in oracle outer join oracle sql outer join outer join in oracle sql database oracle database oracle join oracle inner join oracle sql left join oracle sql oracle sql database sql oracle retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer outer join oracle outer join in oracle outer join oracle sql outer join outer join in oracle sql database oracle database oracle join oracle inner join oracle sql left join oracle sql oracle sql database sql oracle retirement planning retirement

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

online tech support online computer help computer technician computer problems computer outer join oracle outer join in oracle outer join oracle sql outer join outer join in oracle sql database oracle database oracle join oracle inner join oracle sql left join oracle sql oracle sql database sql oracle retirement planning retirement

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;

online tech support online computer help computer technician computer problems computer outer join oracle outer join in oracle outer join oracle sql outer join outer join in oracle sql database oracle database oracle join oracle inner join oracle sql left join oracle sql oracle sql database sql oracle retirement planning retirement

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

 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)

*