Oracle Joins
Version 21c

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Join Types
ANSI Inner Join Cartesian Inner Join ISO 2 Table Inner Join
ANSI Left Outer Join Cartesian Not Inner Join ISO Multi-Table Inner Join
ANSI Right Outer Join Cartesian (Cross-Join) Product ISO Left Outer Join
ANSI Full Outer Join Cross Apply Join ISO Right Outer Join
ANSI Natural Join Outer Apply Join ISO Self Outer Join
ANSI Self Join    
 
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 21c
What's New In 26ai

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