ORACLE JOIN - Online Tech Support

How To Write Oracle Join

online tech support Comments Off

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

How To Write Oracle ANTIJOIN and SEMIJOIN

online tech support Comments Off

The first Oracle Antijoin on this tutorial page will return only rows from the master table that’s values couldn’t find or did not have match in the subquery. The syntax is:

SELECT <columns> 
  FROM <table1> 
 WHERE <columns> NOT IN (SELECT <columns> FROM <table2>);

This Oracle join example will show only query “A” rows that do not exist in query “B“:

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a
WHERE a.id NOT IN ( SELECT rownum + 6 AS Id
                      FROM DUAL
                   CONNECT BY rownum < 11);

This Oracle join will be useful to use in cases where you need to exclude more than one NOT NULL value and when the ID column is indexed it will make the query much faster. Keep in mind that NULL (empty) values in the subquery select part will cause all the query returning no rows. The another way to write antijoin queries is to use NOT EXIST keywords. You could use this query to exclude rows using more complicated conditions in the slave query or in the situations when there is no exact column value to exclude. Also the NOT EXIST condition would work with the possible NULL values in the joining columns. The NOT EXIST antijoin syntax is following:

SELECT <columns> 
  FROM <table1> 
 WHERE NOT EXISTS (SELECT <columns> FROM <table2>);

The next example will return no lines because the Oracle Dual table has a row by default. This Dual table is used as slave query or subquery in Antijoin example:

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a
WHERE NOT EXISTS ( SELECT *                      
                     FROM DUAL );

The second join group is Oracle Semijoin and it will return all rows from the master table that has a match in the slave table. This Semijoin query is using the EXISTS keyword, but without NOT as it was on the last example. The Semijoin syntax is:

SELECT <columns> 
  FROM <table1> 
 WHERE EXISTS (SELECT <columns> FROM <table2>);

This type of Oracle joins can be used when the direct join condition does not exists or it could contain NULL values or there are more than 1 key column whom value should match with the master table. The following join example will return all lines in the master table since the Dual table has always a row by default.

SELECT * FROM (
 SELECT rownum AS Id
   FROM DUAL
CONNECT BY rownum < 11) a
WHERE EXISTS ( SELECT *                      
                 FROM DUAL );

See Also:
Home Previous Page