Oracle Joins
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
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))
ENABLE ROW MOVEMENT;

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

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

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

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

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

CREATE TABLE title (
title_abbrev VARCHAR2(5),
title_name   VARCHAR2(20))
ENABLE ROW MOVEMENT;

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

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, 'Anne', 'Sweet', '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;

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);
 
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;

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    4 |   128 |    6   (17)|
|  1 |  MERGE JOIN                   |          |    4 |   128 |    6   (17)|
|  2 |   TABLE ACCESS BY INDEX ROWID | TITLE    |    5 |   110 |    2    (0)|
|  3 |    INDEX FULL SCAN            | PK_TITLE |    5 |       |    1    (0)|
|* 4 |   SORT JOIN                   |          |    4 |    40 |    4   (25)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    3    (0)|
-----------------------------------------------------------------------------
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;

-------------------------------------------------------------------------------------
| Id | Operation                       | Name           | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                |    9 |   234 |   11   (28)|
|  1 |  SORT ORDER BY                  |                |    9 |   234 |   11   (28)|
|* 2 |  HASH JOIN                      |                |    9 |   234 |   10   (20)|
|  3 |    MERGE JOIN                   |                |    9 |   135 |    6   (17)|
|  4 |     TABLE ACCESS BY INDEX ROWID | PERSON         |    5 |    50 |    2    (0)|
|  5 |      INDEX FULL SCAN            | PK_PERSON      |    5 |       |    1    (0)|
|* 6 |    SORT JOIN                    |                |    9 |    45 |    4   (25)|
|  7 |      TABLE ACCESS FULL          | PERSON_ROLE_IE |    9 |    45 |    3   (0)|
|  8 |    TABLE ACCESS FULL            | PERSON_ROLE    |    6 |    66 |    3    (0)|
-------------------------------------------------------------------------------------
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(+);

-----------------------------------------------------------------
| Id | Operation           | Name   | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT    |        |    5 |   160 |    7   (15)|
|* 1 |  HASH JOIN OUTER    |        |    5 |   160 |    7   (15)|
|  2 |   TABLE ACCESS FULL | PERSON |    5 |    50 |    3    (0)|
|  3 |   TABLE ACCESS FULL | TITLE  |    5 |   110 |    3    (0)|
-----------------------------------------------------------------
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;

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    5 |   160 |    6   (17)|
|  1 | 
MERGE JOIN OUTER             |          |    5 |   160 |    6   (17)|
|  2 |   TABLE ACCESS BY INDEX ROWID | TITLE    |    5 |   110 |    2    (0)|
|  3 |    INDEX FULL SCAN            | PK_TITLE |    5 |       |    1    (0)|
|* 4 |  
SORT JOIN                   |          |    4 |    40 |    4   (25)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    3    (0)|
-----------------------------------------------------------------------------
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;

------------------------------------------------------------------------------
| Id | Operation                      | Name     | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |          |    1 |    58 |    4    (0)|
|  1 |  NESTED LOOPS                  |          |      |       |            |
|  2 |   NESTED LOOPS                 |          |    1 |    58 |    4    (0)|
|  3 |    NESTED LOOPS                |          |    1 |    36 |    3    (0)|
|* 4 |     TABLE ACCESS FULL          | PERSON   |    1 |    14 |    3    (0)|
|  5 |     TABLE ACCESS BY INDEX ROWID| TITLE    |    1 |    22 |    0    (0)|
|* 6 |      INDEX UNIQUE SCAN         | PK_TITLE |    1 |       |    0    (0)|
|* 7 |    INDEX UNIQUE SCAN           | PK_TITLE |    1 |       |    0    (0)|
|  8 |   TABLE ACCESS BY INDEX ROWID  | TITLE    |    1 |    22 |    1    (0)|
------------------------------------------------------------------------------
 
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;

----------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |    4 |   128 |    6   (17)|
|  1 |  MERGE JOIN                  |          |    4 |   128 |    6   (17)|
|  2 |   TABLE ACCESS BY INDEX ROWID| TITLE    |    5 |   110 |    2    (0)|
|  3 |    INDEX FULL SCAN           | PK_TITLE |    5 |       |    1    (0)|
|* 4 |   SORT JOIN                  |          |    4 |    40 |    4   (25)|
|* 5 |    TABLE ACCESS FULL         | PERSON   |    4 |    40 |    3    (0)|
----------------------------------------------------------------------------
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;

-----------------------------------------------------------------
| Id | Operation           | Name   | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|  0 | SELECT STATEMENT    |        |    5 |   160 |    7   (15)|
|* 1 |  HASH JOIN OUTER    |        |    5 |   160 |    7   (15)|
|  2 |   TABLE ACCESS FULL | PERSON |    5 |    50 |    3    (0)|
|  3 |   TABLE ACCESS FULL | TITLE  |    5 |   110 |    3    (0)|
-----------------------------------------------------------------
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;

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    5 |   160 |    6   (17)|
|  1 | 
MERGE JOIN OUTER             |          |    5 |   160 |    6   (17)|
|  2 |   TABLE ACCESS BY INDEX ROWID | TITLE    |    5 |   110 |    2    (0)|
|  3 |    INDEX FULL SCAN            | PK_TITLE |    5 |       |    1    (0)|
|* 4 |  
SORT JOIN                   |          |    4 |    40 |    4   (25)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    3    (0)|
-----------------------------------------------------------------------------
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;

----------------------------------------------------------------------
| Id | Operation              | Name     | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|  0 | SELECT STATEMENT       |          |    5 |   130 |    7   (15)|
|  1 |  VIEW                  | VW_FOJ_0 |    5 |   130 |    7   (15)|
|* 2 |   HASH JOIN FULL OUTER |          |    5 |   160 |    7   (15)|
|  3 |    TABLE ACCESS FULL   | PERSON   |    5 |    50 |    3    (0)|
|  4 |    TABLE ACCESS FULL   | TITLE    |    5 |   110 |    3    (0)|
----------------------------------------------------------------------
Natural Join SELECT <column_name>, <column_name>
FROM <table_name alias> NATURAL [INNER] 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, 'Ted', '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, 'David', 'So What');
COMMIT;

SELECT adult_name, child_name
FROM parents NATURAL JOIN children;

-------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT    |          |    1 |    94 |    7   (15)|
|* 1 | 
HASH JOIN          |          |    1 |    94 |    7   (15)|
|  2 |   TABLE ACCESS FULL | PARENTS  |    2 |    94 |    3    (0)|
|  3 |   TABLE ACCESS FULL | CHILDREN |    3 |   141 |    3    (0)|
-------------------------------------------------------------------
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;

---------------------------------------------------------------------------------
| Id | Operation                       | Name       | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |            |   10 |   150 |    4    (0)|
|* 1 |  COUNT STOPKEY                  |            |      |       |            |
|  2 |   NESTED LOOPS                  |            |      |       |            |
|  3 |    NESTED LOOPS                 |            |   10 |   150 |    4    (0)|
|  4 |     TABLE ACCESS FULL           | SERV_INST  |   10 |    90 |    2    (0)|
|* 5 |     INDEX UNIQUE SCAN           | PK_SERVERS |    1 |       |    0    (0)|
|  6 |     TABLE ACCESS BY INDEX ROWID | SERVERS    |    1 |     6 |    1    (0)|
---------------------------------------------------------------------------------
 
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;

EXPLAIN PLAN FOR
SELECT s.srvr_id, i.location_code
FROM servers s CROSS JOIN serv_inst i;

-------------------------------------------------------------------------------------------
| Id | Operation                     | Name                       |Rows|Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                            |140K|1238K|  276    (1)|
|  1 |  MERGE JOIN CARTESIAN         |                            |140K|1238K|  276    (1)|
|  2 |   BITMAP CONVERSION TO ROWIDS |                            |999 |4995 |    3    (0)|
|  3 |    BITMAP INDEX FAST FULL SCAN| BIX_SERV_INST_LOCATION_CODE|    |     |            |
|  4 |   BUFFER SORT                 |                            | 141| 564 |  273    (1)|
|  5 |    INDEX FAST FULL SCAN       | PK_SERVERS                 | 141| 564 |    0    (0)|
-------------------------------------------------------------------------------------------
A Cross-Join demo that does more than just demonstrate that they can be expensive CREATE TABLE t1 (
part_id VARCHAR2(10),
year VARCHAR2(4),
jan_amount NUMBER,
feb_amount NUMBER,
mar_amount NUMBER,
apr_amount NUMBER,
may_amount NUMBER,
jun_amount NUMBER,
jul_amount NUMBER,
aug_amount NUMBER,
sep_amount NUMBER,
oct_amount NUMBER,
nov_amount NUMBER,
dec_amount NUMBER);

INSERT INTO t1 VALUES ('A', '2012', 1,2,3,4,5,6,7,8,9,10,11,12);
INSERT INTO t1 VALUES ('B', '2012', 1,2,3,4,5,6,7,8,9,10,11,12);

CREATE TABLE t2 (
part_id VARCHAR2(10),
year    VARCHAR2(4),
month   VARCHAR2(3),
amount  NUMBER);

SELECT * FROM t1;

INSERT INTO t2
WITH all_months AS (
  SELECT TO_CHAR(ADD_MONTHS(SYSDATE,LEVEL),'MON','NLS_DATE_LANGUAGE=ENGLISH') AS mth_abbr
  FROM dual
  CONNECT BY LEVEL <= 12)
SELECT x.part_id, x.year, m.mth_abbr, COALESCE(
       CASE m.mth_abbr
       WHEN 'JAN' THEN x.jan_amount
       WHEN 'FEB' THEN x.feb_amount
       WHEN 'MAR' THEN x.mar_amount
       WHEN 'APR' THEN x.apr_amount
       WHEN 'MAY' THEN x.may_amount
       WHEN 'JUN' THEN x.jun_amount
       WHEN 'JUL' THEN x.jul_amount
       WHEN 'AUG' THEN x.aug_amount
       WHEN 'SEP' THEN x.sep_amount
       WHEN 'OCT' THEN x.oct_amount
       WHEN 'NOV' THEN x.nov_amount
       WHEN 'DEC' THEN x.dec_amount
       END, 0) AS amount
FROM t1 x
CROSS JOIN all_months m;

SELECT * FROM t2;
 
Join Explain Plan Demos
Antijoin conn hr/hr@pdborcl

explain plan for
SELECT * FROM employees
WHERE department_id NOT IN (
  SELECT department_id FROM departments
  WHERE location_id = 1700);

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id | Operation                     | Name             | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                  |  106 |  7950 |    6   (17)|
|* 1 |   HASH JOIN RIGHT ANTI SNA    |                  |  106 |  7950 |    6   (17)|
|  2 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |   21 |   147 |    2    (0)|
|* 3 |     INDEX RANGE SCAN          | DEPT_LOCATION_IX |   21 |       |    1    (0)|
|  4 |    TABLE ACCESS FULL          | EMPLOYEES        |  107 |  7276 |    3    (0)|
-------------------------------------------------------------------------------------
Semijoin conn hr/hr@pdborcl

EXPLAIN PLAN FOR
SELECT * FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500);

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id | Operation                  | Name        | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT           |             |   10 |   270 |    6   (17)|
|  1 | MERGE JOIN SEMI            |             |   10 |   270 |    6   (17)|
|  2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |   27 |   540 |    2    (0)|
|  3 | INDEX FULL SCAN            | DEPT_ID_PK  |   27 |       |    1    (0)|
|* 4 | SORT UNIQUE                |             |  105 |   735 |    4   (25)|
|* 5 | TABLE ACCESS FULL          | EMPLOYEES   |  105 |   735 |    3    (0)|
-----------------------------------------------------------------------------
 
Join Related Queries
Column Join Usage conn sys@pdborcl as sysdba

set linesize 121

desc col_usage$

SELECT *
FROM col_usage$
WHERE obj# IN (
  SELECT object_id
  FROM dba_objects_ae
  WHERE owner = 'UWCLASS');

Related Topics
DML Statements
INSERT Statements
MERGE Statements
SELECT Statements
UPDATE Statements
Where Clause

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved