PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Joins
Version 11.1
 
Demo Tables & Data

Join Demo Tables		 CREATE TABLE person (
person_id     NUMBER(10),
first_name    VARCHAR2(25) NOT NULL,
last_name     VARCHAR2(25) NOT NULL,
title_1       VARCHAR2(5),
title_2       VARCHAR2(5))
PCTFREE 20;

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role (
role_id   VARCHAR2(1),
role_name VARCHAR2(20) NOT NULL);

ALTER TABLE person_role
ADD CONSTRAINT pk_role
PRIMARY KEY (role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE person_role_ie (
person_role_id NUMBER(10),
person_id      NUMBER(10) NOT NULL,
role_id        VARCHAR2(1) NOT NULL);

ALTER TABLE person_role_ie
ADD CONSTRAINT pk_person_role_ie
PRIMARY KEY (person_role_id)
USING INDEX
PCTFREE 5;

CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name   VARCHAR2(20))
PCTFREE 0;

ALTER TABLE title
ADD CONSTRAINT pk_title
PRIMARY KEY (title_abbrev)
USING INDEX
PCTFREE 0;

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_person
FOREIGN KEY (person_id)
REFERENCES person(person_id);

ALTER TABLE person_role_ie
ADD CONSTRAINT fk_person_role_ie_role
FOREIGN KEY (role_id)
REFERENCES person_role(role_id);

ALTER TABLE person
ADD CONSTRAINT fk_person_title1
FOREIGN KEY (title_1)
REFERENCES title(title_abbrev);

ALTER TABLE person
ADD CONSTRAINT fk_person_title2
FOREIGN KEY (title_2)
REFERENCES title(title_abbrev);

Demo Table Data Load 		INSERT INTO title VALUES
('BA', 'Bachelor of Arts');

INSERT INTO title VALUES
('BS', 'Bachelor of Science');

INSERT INTO title VALUES
('MS', 'Master of Science');

INSERT INTO title VALUES
('PhD', 'Doctor of Philosophy');

INSERT INTO title VALUES
('MD', 'Doctor of Medicine');

INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(1, 'Daniel', 'Morgan', 'BS');

INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(2, 'Jack', 'Cline', 'BA');

INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(3, 'Muriel', 'Dance', 'PhD');

INSERT INTO person
(person_id, first_name, last_name, title_1)
VALUES
(4, 'Elizabeth', 'Scott', 'MS');

INSERT INTO person
(person_id, first_name, last_name)
VALUES
(5, 'Jacqueline', 'Stough');

INSERT INTO person_role VALUES (1, 'Administrator');
INSERT INTO person_role VALUES (2, 'Professor');
INSERT INTO person_role VALUES (3, 'Instructor');
INSERT INTO person_role VALUES (4, 'Employee');
INSERT INTO person_role VALUES (5, 'Student');
INSERT INTO person_role VALUES (9, 'Alumni');

CREATE SEQUENCE seq_pr_id START WITH 1;

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 2);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 9);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 2, 3);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 1, 5);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 1);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 3, 9);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 4, 4);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 5);

INSERT INTO person_role_ie VALUES
(seq_pr_id.NEXTVAL, 5, 9);

COMMIT;
 
Traditional Joins

Two Table Inner Join 		SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev;

Three Table Inner Join 		SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> = <alias.column_name>
AND <alias.column_name> = <alias.column_name>;
SELECT p.last_name, r.role_name
FROM person p, person_role_ie i, person_role r
WHERE p.person_id = i.person_id
AND i.role_id = r.role_id
ORDER BY p.person_id;

Left Outer Join 		SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name = <alias.column_name>
AND <alias.column_name> = <alias.column_name> (+);
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev(+);

Right Outer Join 		SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> (+) = <alias.column_name>;
SELECT p.last_name, t.title_name
FROM person p, title t
 WHERE p.title_1(+) = t.title_abbrev;

Self Join 		SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>, <table_name alias>
WHERE <alias.column_name> = < alias.column_name>
AND <alias.column_name> = <alias.column_name>;
UPDATE person
SET title_2 = 'PhD'
WHERE person_id = 1;
COMMIT;

SELECT p.last_name, t1.title_name, t2.title_name
FROM person p, title t1, title t2
WHERE p.title_1 = t1.title_abbrev
AND p.title_2 = t2.title_abbrev;
 
ANSI Joins

Inner Join 		SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p INNER JOIN title t
ON p.title_1 = t.title_abbrev;

Left Outer Join 		SELECT <column_name>, <column_name>
FROM <table_name alias> LEFT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p LEFT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;

Right Outer Join 		SELECT <column_name>, <column_name>
FROM <table_name alias> RIGHT OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p RIGHT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;

Full Outer Join 		SELECT <column_name>, <column_name>
FROM <table_name alias> FULL OUTER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>
SELECT p.last_name, t.title_name
FROM person p FULL OUTER JOIN title t
ON p.title_1 = t.title_abbrev;

Natural Join 		SELECT <column_name>, <column_name>
FROM <table_name alias> NATURAL JOIN <table_name alias>
CREATE TABLE parents (
person_id  NUMBER(5),
adult_name VARCHAR2(20),
comments   VARCHAR2(40))
PCTFREE 0;

CREATE TABLE children (
parent_id    NUMBER(5),
person_id    NUMBER(5),
child_name   VARCHAR2(20),
comments     VARCHAR2(40))
PCTFREE 0;

INSERT INTO parents VALUES (1, 'Dan', 'So What');
INSERT INTO parents VALUES (2, 'Jack', 'Who Cares');
INSERT INTO children VALUES (1, 2, 'Anne', 'Who Cares');
INSERT INTO children VALUES (1, 1, 'Julia', 'Yeah Right');
INSERT INTO children VALUES (2, 1, 'Marcella', 'So What');
COMMIT;

SELECT adult_name, child_name
FROM parents NATURAL JOIN children;

Self Join 		SELECT <column_name>, <column_name>
FROM <table_name alias> INNER JOIN <table_name alias>
ON <alias.column_name> = <alias.column_name>,
   <table_name alias> INNER JOIN <table_name alias>
ON <alias .column_name> = <alias.column_name>;
SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1
ON p1.title_1 = t1.title_abbrev,
     person p2 INNER JOIN title t2
ON p2.title_2 = t2.title_abbrev;

EXPLAIN PLAN FOR
SELECT p1.last_name, t1.title_name, t2.title_name
FROM person p1 INNER JOIN title t1
ON p1.title_1 = t1.title_abbrev,
   person p2 INNER JOIN title t2
ON p2.title_2 = t2.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------
| Id |            Operation            |   Name   | Rows | Cost (%CPU)|
-----------------------------------------------------------------------
|  0 | SELECT STATEMENT                |          |    1 |   16    (0)|
|  1 |  NESTED LOOPS                   |          |      |            |
|  2 |   NESTED LOOPS                  |          |    1 |   16    (0)|
|  3 |    MERGE JOIN CARTESIAN         |          |    1 |   15    (0)|
|  4 |     NESTED LOOPS                |          |      |            |
|  5 |      NESTED LOOPS               |          |    1 |   10    (0)|
|  6 |       TABLE ACCESS FULL         | PERSON   |    5 |    5    (0)|
|  7 |       INDEX UNIQUE SCAN         | PK_TITLE |    1 |    0    (0)|
|  8 |      TABLE ACCESS BY INDEX ROWID| TITLE    |    1 |    1    (0)|
|  9 |     BUFFER SORT                 |          |    5 |   14    (0)|
| 10 |      TABLE ACCESS FULL          | PERSON   |    5 |    5    (0)|
| 11 |    INDEX UNIQUE SCAN            | PK_TITLE |    1 |    0    (0)|
| 12 |   TABLE ACCESS BY INDEX ROWID   | TITLE    |    1 |    1    (0)|
-----------------------------------------------------------------------

Alternative Syntax Joining on commonly named column in both tables		 SELECT <column_name>, <column_name>
FROM <table_name alias> <join_type> <table_name alias>
USING (<common_column_name>);
-- does not work
SELECT s.srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (s.srvr_id)
WHERE rownum < 11;

-- does not work either
SELECT s.srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (srvr_id)
WHERE rownum < 11;

-- works
SELECT srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (srvr_id)
WHERE rownum < 11;
 
Cartesian Join
Table And Data For Cartesian
Product (Cross-Join) Demo 		CREATE TABLE cartesian (
join_column NUMBER(10));

CREATE TABLE product (
join_column NUMBER(10));

Load Demo Tables		 BEGIN
  FOR i in 1..1000
  LOOP
    INSERT INTO cartesian VALUES (i);
    INSERT INTO product VALUES (i);
  END LOOP;
  COMMIT;
END;
/

Inner Join		 SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column = p.join_column;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column = p.join_column;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------
| Id | Operation           |    Name   | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |    1 |    26 |   11   (10)|
|  1 |  SORT AGGREGATE     |           |    1 |    26 |            |
|  2 |   HASH JOIN         |           | 1000 | 26000 |   11   (10)|
|  3 |    TABLE ACCESS FULL| CARTESIAN | 1000 | 13000 |    5    (0)|
|  4 |    TABLE ACCESS FULL| PRODUCT   | 1000 | 13000 |    5    (0)|
--------------------------------------------------------------------

Not Inner Join		 SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column != p.join_column;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM cartesian c, product p
WHERE c.join_column != p.join_column;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------
| Id |      Operation      |    Name   | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|  0 | SELECT STATEMENT    |           |    1 |    26 | 3076    (2)|
|  1 |  SORT AGGREGATE     |           |    1 |    26 |            |
|  2 |   NESTED LOOPS      |           |  999K|    24M| 3076    (2)|
|  3 |    TABLE ACCESS FULL| CARTESIAN | 1000 | 13000 |    5    (0)|
|  4 |    TABLE ACCESS FULL| PRODUCT   |  999 | 12987 |    3    (0)|
--------------------------------------------------------------------

Cartesian (Cross-Join) Product		 SELECT COUNT(*)
FROM cartesian, product;

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM cartesian c, product p;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------
| Id |       Operation       |    Name   | Rows | Cost (%CPU)|
----------------------------------------------------------------
|  0 | SELECT STATEMENT      |           |    1 | 3076    (2)|
|  1 |  SORT AGGREGATE       |           |    1 |            |
|  2 |   MERGE JOIN CARTESIAN|           | 1000K| 3076    (2)|
|  3 |    TABLE ACCESS FULL  | CARTESIAN | 1000 |    5    (0)|
|  4 |    BUFFER SORT        |           | 1000 | 3071    (2)|
|  5 |     TABLE ACCESS FULL | PRODUCT   | 1000 |    3    (0)|
----------------------------------------------------------------

Intentional Cartesian (Cross-Join) Product		 SELECT <alias.column_name>, <alias.column_name>
FROM <table_name alias> CROSS JOIN <table_name alias>;
SELECT s.srvr_id, i.location_code
FROM servers s CROSS JOIN serv_inst i
WHERE rownum < 1001;

 
 
Join Related Queries

Column Join Usage		 conn / as sysdba

set linesize 121

desc col_usage$

SELECT *
FROM col_usage$
WHERE obj# IN (
  SELECT object_id
  FROM dba_objects
  WHERE owner = 'UWCLASS');
 
Related Topics
Select
Where Clause
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [95 users online]    © 2010 psoug.org