How To Write An Oracle JOIN

There are more than a few joins in Oracle database and the same join can be written in many different ways. We will write the first Oracle join as in Oracle style by “nature” and the second example with the same type in ANSI standard style. The main purpose of the joins is to connect a table with itself (as selfjoin) or many different tables using the same data value. We will use the following two SQL queries as so called “source” tables and they will be used in the examples below. So you don’t have to create any table to try out the joins. The first sql query returns Id number values from 1 to 10 and we are going to use them to join different tables as it would be a real Id column in a table.

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

The second query has Id numbers from 7 to 16, so it wouldn’t be an exact match as sometimes can happen in a database. There you can have two different tables that don’t have equal amount and type of Id numbers. Also having some differences will make the Oracle DBA examples more clear and easier to understand.

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

When you take a look at those last two queries you see the common Id values are in both tables 7,8,9 and 10. Now we will join this two tables and the output looks following.

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;

The query above just returned 100 lines even we did have 10 lines in the first table and 10 lines another one. How did that happened?

Look at the SQL query one more time there is no join condition between those two tables and no any database can understand how do you want to join those two tables. We should use the Id columns to join the tables. The following example will join the both tables using Id columns and we are doing it in the WHERE 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, my_b
WHERE my_a.a_id = my_b.b_id;

The join operator has returned on this time only 4 lines, because we have only 4 Id values matching in both tables. This type of join is name as Oracle equijoin. The equijoin in Oracle returns only rows who’s joining columns has exactly the same value. And the equijoin looks the same in ANSI SQL.

The next Oracle join is non-equijoin and the difference with the last join is that the condition is doing not equal comparison as the following example shows:

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 b_id < a_id;

The output shows all lines where table “B” Id number is smaller than in table “A” which is not equal. The first column from left to right belongs to table “A” and column “ID_1” belongs to table “B“.

The third type of joins in Oracle are Oracle Selfjoin. Basically this Oracle join type is using the same table twice or more times. The following example needs a different table to show better the joining method. For example we have a table with people’s details and that table also has every person saved with his/her unique Id value, and the second column is for names. The table has 2 columns more where the first is father_id and the second is mother_id. We will keep in those columns reference to the person’s biological father and mother who also are stored to the same table as other people. The following Oracle DBA example will return a person and the names 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 output shows that there is somebody named “Jon Dowe” and he has father’s name is “Phil Dowe” and his mother’s name is “Sandra Dowe”. All three people are coming from the same table that has been used 3 times. This example conditions are assuming that we do have every persons’ mother and father registered so there isn’t any missing references to a father or to a mother.

Next Page

See Also:
Oracle Select Oracle Rownum Home