Oracle Joins
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
Join Types
 
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);

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);

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);

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>
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    4 |   128 |   12    (9)|
|  1 |  MERGE JOIN                   |          |    4 |   128 |   12    (9)|
|  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 |   10   (10)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    9    (0)|
-----------------------------------------------------------------------------
Multi-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>
EXPLAIN PLAN FOR
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;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------------
| Id | Operation                       | Name          | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                |     9 |  243 |     21  (5)|
|  1 |  NESTED LOOPS                  |                |     9 |  243 |     21  (5)|
|  2 |   NESTED LOOPS                 |                |     9 |  243 |     21  (5)|
|  3 |    MERGE JOIN                  |                |     9 |  135 |     12  (9)|
|  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 |     10 (10)|
|  7 |      TABLE ACCESS FULL         | PERSON_ROLE_IE |     9 |   45 |      9  (0)|
|* 8 |    INDEX UNIQUE SCAN           | PK_ROLE        |     1 |      |      0  (0)|
|  9 |   TABLE ACCESS BY INDEX ROWID  | PERSON_ROLE    |     1 |   12 |      1  (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> (+)
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1 = t.title_abbrev(+);

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |    5 |   160 |   13    (0)|
|* 1 |  NESTED LOOPS OUTER          |          |    5 |   160 |   13    (0)|
|  2 |   TABLE ACCESS FULL          | PERSON   |    5 |    50 |    9    (0)|
|  3 |   TABLE ACCESS BY INDEX ROWID| TITLE    |    1 |    22 |    1    (0)|
|  4 |    UNIQUE INDEX SCAN         | PK_TITLE |    1 |       |    0    (0)|
----------------------------------------------------------------------------
Right Outer Join SELECT <column_name>, <column_name>
FROM <table_name alias>, <table_name alias>
WHERE <alias.column_name> (+) = <alias.column_name>;
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p, title t
WHERE p.title_1(+) = t.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    5 |   160 |   12   (17)|
|  1 | 
MERGE JOIN OUTER             |          |    5 |   160 |   12   (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 |   10   (25)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    9    (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;

EXPLAIN PLAN FOR
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;

SELECT * FROM TABLE(dbms_xplan.display);

------------------------------------------------------------------------------
| Id | Operation                     | Name      | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |          |    1 |    58 |     10  (0)|
|  1 |  NESTED LOOPS                  |          |    1 |    58 |     10  (0)|
|  2 |   NESTED LOOPS                 |          |    1 |    58 |     10  (0)|
|  3 |    NESTED LOOPS                |          |    1 |    36 |      9  (0)|
|* 4 |     TABLE ACCESS FULL          | PERSON   |    1 |    14 |      9  (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)|
------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
 
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>
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p INNER JOIN title t
ON p.title_1 = t.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    4 |   128 |   12    (9)|
|  1 |  MERGE JOIN                   |          |    4 |   128 |   12    (9)|
|  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 |   10   (10)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    9    (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>
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p LEFT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------
| Id | Operation                    | Name     | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |          |    5 |   160 |   13    (0)|
|* 1 |  NESTED LOOPS OUTER          |          |    5 |   160 |   13    (0)|
|  2 |   TABLE ACCESS FULL          | PERSON   |    5 |    50 |    9    (0)|
|  3 |   TABLE ACCESS BY INDEX ROWID| TITLE    |    1 |    22 |    1    (0)|
|  4 |    UNIQUE INDEX SCAN         | PK_TITLE |    1 |       |    0    (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>
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p RIGHT OUTER JOIN title t
ON p.title_1 = t.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------------
| Id | Operation                     | Name     | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |          |    5 |   160 |   12   (17)|
|  1 | 
MERGE JOIN OUTER             |          |    5 |   160 |   12   (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 |   10   (25)|
|* 5 |    TABLE ACCESS FULL          | PERSON   |    4 |    40 |    9    (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>
EXPLAIN PLAN FOR
SELECT p.last_name, t.title_name
FROM person p FULL OUTER JOIN title t
ON p.title_1 = t.title_abbrev;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------
| Id | Operation              | Name     | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|  0 | SELECT STATEMENT       |          |    6 |   156 |     18  (0)|
|  1 |  VIEW                  | VW_FOJ_0 |    6 |   156 |     18  (0)|
|* 2 |   HASH JOIN FULL OUTER |          |    6 |   192 |     18  (0)|
|  3 |    TABLE ACCESS FULL   | PERSON   |    5 |    50 |      9  (0)|
|  4 |    TABLE ACCESS FULL   | TITLE    |    5 |   110 |      9  (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;

EXPLAIN PLAN FOR
SELECT adult_name, child_name
FROM parents NATURAL JOIN children;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT    |          |    2 |   188 |   18    (0)|
|* 1 | 
HASH JOIN          |          |    2 |   188 |   18    (0)|
|  2 |   TABLE ACCESS FULL | PARENTS  |    2 |    94 |    9    (0)|
|  3 |   TABLE ACCESS FULL | CHILDREN |    3 |   141 |    9    (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>;
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 | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |          |    4 |   224 |    23   (0)|
|  1 |  NESTED LOOPS                   |          |    4 |   224 |    23   (0)|
|  2 |   NESTED LOOPS                  |          |    4 |   224 |    23   (0)|
|  3 |    MERGE JOIN CARTESIAN         |          |    4 |   136 |    19   (0)|
|  4 |     NESTED LOOPS                |          |    1 |    24 |    10   (0)|
|  5 |      NESTED LOOPS               |          |    1 |    24 |    10   (0)|
|  6 |       TABLE ACCESS FULL         | PERSON   |    1 |     2 |     9   (0)|
|  7 |       INDEX UNIQUE SCAN         | PK_TITLE |    1 |       |     0   (0)|
|  8 |      TABLE ACCESS BY INDEX ROWID| TITLE    |    1 |    22 |     1   (0)|
|  9 |     BUFFER SORT                 |          |    4 |    40 |    18   (0)|
| 10 |      TABLE ACCESS FULL          | PERSON   |    4 |    40 |     9   (0)|
| 11 |    INDEX UNIQUE SCAN            | PK_TITLE |    1 |       |     0   (0)|
| 12 |   TABLE ACCESS BY INDEX ROWID   | TITLE    |    1 |    22 |     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
EXPLAIN PLAN FOR
SELECT srvr_id, s.status, i.location_code
FROM servers s INNER JOIN serv_inst i
USING (srvr_id)
WHERE rownum < 11;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------------------
| Id | Operation                       | Name       | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |            |   10 |   150 |    5    (0)|
|* 1 |  COUNT STOPKEY                  |            |      |       |            |
|  2 |   NESTED LOOPS                  |            |   10 |   150 |    5    (0)|
|  3 |    NESTED LOOPS                 |            |   10 |   150 |    5    (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 Joins
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 |   18    (0)|
|  1 |  SORT AGGREGATE     |           |    1 |    26 |            |
|  2 |   HASH JOIN         |           | 1000 | 26000 |   18    (0)|
|  3 |    TABLE ACCESS FULL| CARTESIAN | 1000 | 13000 |    9    (0)|
|  4 |    TABLE ACCESS FULL| PRODUCT   | 1000 | 13000 |    9    (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 | 7611    (1)|
|  1 |  SORT AGGREGATE     |           |    1 |    26 |            |
|  2 |   NESTED LOOPS      |           |  999K|    24M| 7611    (1)|
|  3 |    TABLE ACCESS FULL| CARTESIAN | 1000 | 13000 |    9    (0)|
|  4 |    TABLE ACCESS FULL| PRODUCT   |  999 | 12987 |    8    (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 | 7608    (1)|
|  1 |  SORT AGGREGATE       |           |    1 |            |
|  2 |   MERGE JOIN CARTESIAN|           | 1000K| 7608    (1)|
|  3 |    TABLE ACCESS FULL  | CARTESIAN | 1000 |    9    (0)|
|  4 |    BUFFER SORT        |           | 1000 | 7599    (1)|
|  5 |     TABLE ACCESS FULL | PRODUCT   | 1000 |    8    (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;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------------------------
| 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)|
-------------------------------------------------------------------------------------------
Cross-Join demo demonstrating how expensive they can be 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', '2017', 1,2,3,4,5,6,7,8,9,10,11,12);
INSERT INTO t1 VALUES ('B', '2017', 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;
Cross Apply Join

A variation of an ANSI CROSS JOIN in which only rows from the table on the left side of the join that produce a result set from the table_reference or collection_expression are returned
SELECT <column_name_list>
FROM <schema_name.object_name> [object_alias]
CROSS APPLY (
  <inline_view_select_statement> <inline_view_alias>
WHERE <filter_condition>
[ORDER BY <column_list>]
SQL> SELECT d.department_name, v.employee_id, v.last_name
  2  FROM departments d
  3  CROSS APPLY (
  4    SELECT *
  5    FROM employees e
  6    WHERE e.department_id = d.department_id) v
  7  WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
  8  ORDER BY d.department_name, v.employee_id;

DEPARTMENT_NAME   EMPLOYEE_ID LAST_NAME
----------------- ----------- ----------
Marketing                 201 Hartstein
Marketing                 202 Fay
Public Relations          204 Baer

---------------------------------------------------------------------------------------
| Id | Operation                     | Name               | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                   |   29 |   899 |     5  (20)|
|  1 |  SORT ORDER BY                 |                   |   29 |   899 |     5  (20)|
|  2 |   NESTED LOOPS                 |                   |   29 |   899 |     4   (0)|
|  3 |    NESTED LOOPS                |                   |   30 |   899 |     4   (0)|
|* 4 |     TABLE ACCESS FULL          | DEPARTMENTS       |    3 |    48 |     3   (0)|
|* 5 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |   10 |       |     0   (0)|
|  6 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |   10 |   150 |     1   (0)|
---------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
Cross Outer Apply Join aka Outer Apply Join

A variation of an ANSI CROSS JOIN or an ANSI LEFT OUTER JOIN with left correlation support. You can specify a table_reference or collection_expression to the right of the APPLY keyword. The table_reference can be a table, inline view, or TABLE collection expression. The collection_expression can be a subquery, a column, a function, or a collection constructor.

Regardless of its form, it must return a collection value — that is, a value whose type is nested table or varray. The table_reference or collection_expression can reference columns of tables defined in the FROM clause to the left of the APPLY keyword. This is called left correlation.

Specify OUTER APPLY to perform a variation of an ANSI LEFT OUTER JOIN. All rows from the table on the left side of the join are returned. Rows that do not produce a result set from table_reference or collection_expression have the NULL value in the corresponding column(s).
SELECT <column_name_list>
FROM <schema_name.object_name> [object_alias]
OUTER APPLY (
  <inline_view_select_statement> <inline_view_alias>
WHERE <filter_condition>
[ORDER BY <column_list>]
SQL> SELECT d.department_name, v.employee_id, v.last_name
  2  FROM departments d
  3  OUTER APPLY (
  4    SELECT *
  5    FROM employees e
  6    WHERE e.department_id = d.department_id) v
  7  WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
  8  ORDER by d.department_name, v.employee_id;

DEPARTMENT_NAME   EMPLOYEE_ID LAST_NAME
----------------- ----------- -------------------------
Marketing                 201 Hartstein
Marketing                 202 Fay
Operations
Public Relations          204 Baer

------------------------------------------------------------------------------------------------
| Id | Operation                             | Name              | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                      |                   |   29 |   899 |     5  (20)|
|  1 |  SORT ORDER BY                        |                   |   29 |   899 |     5  (20)|
|  2 |   NESTED LOOPS OUTER                  |                   |   29 |   899 |     4   (0)|
|* 3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |    3 |    48 |     3   (0)|
|  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |   10 |   150 |     1   (0)|
|* 5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |   10 |       |     0   (0)|
-------------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan
 
Join Explain Plan Demos
Antijoin conn hr/hr@pdbdev

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@pdbdev

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@pdbdev 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
Lateral Inline View
MERGE Statements
SELECT Statements
UPDATE Statements
Where Clause
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx