The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

JOINs in Oracle (some fundamental thoughts)

In many forums and in personal experience I have come across people asking some basic questions on JOINS in Oracle (for that matter in general relational databases).

One of the five basic relational databases (in theory and practice) is JOIN operation (strictly speaking it is cartesian product). The other four are SELECTION, PROJECTION, UNION and MINUS. More on this can be found at wiki pages: http://en.wikipedia.org/wiki/Relational_algebra. You may feel it little boring, but trust me it is very rich ;-)

As Tom Kyte said, "Oracle (database) is born to Join". That is so very true and yet many developers and programmers almost fear joining tables. Join operation in Oracle (and in other databases) because of two different syntaxes: traditional and ANSI. One of the effective way, in addition to reading the documentations, to learn is to try out some examples.

In this blog post, I hope to provide some basic introduction to JOINs in Oracle. The basic documentation can be found in Oracle manuals.

First let us do some setup.

-- SETUP for examples...
drop table A;
drop table B;
drop table C;

create table a (ca int, sa varchar2(50));

create table b (cb int, sb varchar2(50));

create table c (cc int, sc varchar2(50));

insert into A
select * from
(
select level myid, to_char( to_date( level,'J'),'Jsp') mystr
from dual connect by level < 11
);
commit;

insert into B
select * from
(
select level myid, to_char( to_date( level,'J'),'Jsp') mystr
from dual connect by level < 21
)
where mod(myid,2) = 0;
commit;

insert into C
select * from
(
select level myid, to_char( to_date( level,'J'),'Jsp') mystr
from dual connect by level < 11
)
where myid < 6;
commit;

The SQL statements above sets up three tables and populate them with some "meaningless" data. I believe meaninglessness important. When traditional employees-department or books-library examples are used there is a presumption on the meaning of data resulting in the focus and expectation clouding the essence, which is JOIN operation.

Let us verify our data / setup

-- Verify the setup
select * from A;

select * from B;

select * from C;

First we'll see the most basic join operation.

Requirement EQUI JOIN:

To select rows from A with MATCHING rows from B. Use A join B or B join A

-- **************************************************************
-- Requirement EQUI JOIN 1. (AKA INNER JOIN)
-- To select rows from A and MATCHING rows from B
-- use A JOIN B or B JOIN A 
--
-- ANSI SQL syntax
select * from 
A JOIN B
on (A.CA = B.CB)
;
-- **** OR ****
select * from 
B JOIN A
on (A.CA = B.CB)
;
-- OR Traditional Oracle syntax
select * from A, B
where A.CA = B.CB
;

Requirement EQUI JOIN 2.

To select rows from A and MATCHING rows from B and matching rows from C
Use A JOIN B JOIN C

-- **************************************************************
-- Requirement EQUI JOIN 2. (AKA INNER JOIN)
-- To select rows from A and MATCHING rows from B and matching
-- rows from C
-- use A JOIN B JOIN C
--
-- ANSI SQL syntax
select * from 
A JOIN B
on (A.CA = B.CB)
JOIN C
on(B.CB = C.CC)
;
-- OR Traditional Oracle syntax
select * from A, B, C
where A.CA = B.CB
  and B.CB = C.CC
;

Requirement OUTER JOIN 1:
To select ALL rows from A and ONLY MATCHING rows from B
Use A LEFT OUTER JOIN B or B RIGHT OUTER JOIN A

-- **************************************************************
-- Requirement OUTER JOIN 1.
-- To select ALL rows from A and ONLY MATCHING rows from B
-- use A LEFT OUTER JOIN B or B RIGHT OUTER JOIN A
--
-- The columns for table B will have NULL value where a
-- match for A is not found
--
-- ANSI SQL syntax
select * from 
A LEFT OUTER JOIN B
on (A.CA = B.CB)
;
-- **** OR ****
select * from 
B RIGHT OUTER JOIN A
on (A.CA = B.CB)
;
-- OR Traditional Oracle syntax
select * from A, B
where A.CA = B.CB(+)
;

NOTE: The queries above are EQUIVALENT, theay return the SAMe result set; However notice the order in which they resturn the rows.

There is no meaning for ORDER in relation algebra/mathematics. They all are RESULT SETS.

The only and ONLY way to ensure the result set is ordered is by using ORDER BY clause.

Requirement OUTER JOIN 2:
To select ALL rows from B and ONLY MATCHING rows from A
Use B LEFT OUTER JOIN A or A RIGHT OUTER JOIN B

-- **************************************************************
-- Requirement OUTER JOIN 2.
-- To select ALL rows from B and ONLY MATCHING rows from A
-- use B LEFT OUTER JOIN A or A RIGHT OUTER JOIN B
--
-- The columns for table A will have NULL value where a
-- match for B is not found
--
-- ANSI SQL syntax
select * from 
B LEFT OUTER JOIN A
on (A.CA = B.CB)
;
-- **** OR ****
select * from 
A RIGHT OUTER JOIN B
on (A.CA = B.CB)
;
-- Traditional Oracle syntax
select * from A, B
where A.CA(+) = B.CB
;

Requirement OUTER JOIN 3
To select ALL rows from A and ONLY MATCHING rows from B AND select those rows from C that match rows from B

-- **************************************************************
-- Requirement OUTER JOIN 3.
-- To select ALL rows from A and ONLY MATCHING rows from B
-- AND select those rows from C that match rows from B
--
-- ANSI SQL syntax
select * from 
A LEFT OUTER JOIN B
on (A.CA = B.CB)
 LEFT OUTER JOIN C
on (B.CB = C.CC)
;
-- OR Traditional Oracle syntax
select * from A, B, C
where A.CA = B.CB(+)
  and B.CB = C.CC(+)
;

----------------------------------------------------------
-- and So on...
 

We started of with CARTESIAN PRODUCT... what is that?

Requirement FULL JOIN:
To select ALL rows from A and ALL rows from B AND MATCH the rows from A and B that match, but return even the unmatching rows. 

-- **************************************************************
-- Requirement FULL JOIN 3.
-- To select ALL rows from A and ALL rows from B
-- AND MATCH the rows from A and B that match, but return even 
-- the unmatching rows.
--
-- ANSI SQL syntax
select * from 
A FULL OUTER JOIN B
on (A.CA = B.CB)
;
-- OR Traditional Oracle syntax
-- There is no equivalent for FULL OUTER join in traditional 
-- syntax. (Though it can be achieved using other means)
-----------------------------------------------------------------

Happy Joining and good luck!

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?