Online Tech Support

This Online Tech Support learning page is about how to write different join statement in Oracle. There are more then a few join type in Oracle SQL and the same oracle join can be written in different ways. To explain better these different ways the same join SQL is written as the first join as an Oracle nature style and the second is in ANSI standard. The main purpose of the joins is to connect one or many tables using the same column value. The following two tables for the join examples more below.

 SELECT rownum  AS Id
   FROM DUAL
CONNECT BY rownum < 11;

The first query returns rows from 1 to 10. And the second query from 7 to 16:

 SELECT rownum + 6  AS Id
   FROM DUAL
CONNECT BY rownum < 11;

If you’ll compare the both queries you’ll find the common values in both outputs are numbers 7,8,9 and 10. Now in this first query we’ll join this two tables the result looks below.

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;

We did have 10 lines in one query and 10 lines in second query but the join returned 100 lines. Why is it so?
The reason is that we didn’t join the queries using the Id column values. To “fix” the join take a look at following example there we will join the both queries using Id columns. The condition is declared in the WHERE block.

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;

The join above returned only 4 lines and this is so because only 4 lines has matching values in both queries and for this reason the join is name as Oracle equijoin. The equijoin returns only rows that have in the column the same value. About the ANSI SQL the equijoin join looks the same in there too so there is no need for a second ANSI example.

The second join group is non-equijoin and the difference with the last join is that this condition is based on not equal comparison as the following example shows:

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 b.id < a.id;

The non-equijoin as the equijoin has no need for the extra ANSI example.

The third types of joins are Oracle selfjoin. Basically they are done using the same table twice. For this example we have to use a different table than through earlier examples above. We have a new table named people that stores unique Id values and names for earch person. Plus additional 2 columns that are the first is father_id and the second is mother_id. We will keep in this columns a reference to the person’s biological father and mother who are also stored as persons in the same people table. The example below will return a person and the name of his/her father and mother.

SELECT per.name AS person, fat.name AS father_name, mot.name AS mother_name 
 FROM people per, people fat, people mot
WHERE per.father_id = fat.id
  AND per.mother_id = mot.id;

The last example condition assumes that we do have every persons’ mother and father registered so there isn’t any missing father_id or mother_id value and that way we would not miss any data or have empty columns.

Next Page