Join types and their implementation using ANSI JOIN syntax in Oracle: Oracle, Join, ANSI

Introduction

A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with generic join types. I think that has caused some confusion because Cartesian product, Equijoin, Non-equijoin, Outer join and Self join are not in any way proprietary to Oracle per se. Rather, Oracle has had and still supports some proprietary extensions (such as the + operator in the outer join) and implementations of those constructs. Since Oracle now (since version 9i) fully supports the more powerful and elegant ANSI JOIN syntax it should always be used in preference to any proprietary implementation

This article strives to:

describe the ANSI SQL syntax

describe the various generic join types and how they can be implemented using the ANSI SQL syntax

Join basics

Most non-trivial SQL queries involve joining one or more tables together. The general syntax can be defined as follows:

SELECT
  <column definition>
FROM
  left_table

<JOIN DEFINITION>
  right_table
<JOIN CONDITION>
;

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

More than two tables can be joined together by adding more right tables:

SELECT
  <column definition>
FROM
  left_table

<JOIN DEFINITION>
  right_table
<JOIN CONDITION>

<JOIN DEFINITION>
  right_table2
<JOIN CONDITION>
...
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select all Open in new window

The above syntax complies with the ANSI SQL standard which is supported by Oracle starting with version 9i. Prior to 9i the join syntax used in Oracle was proprietary and looked like this:

SELECT
  <column definition>
FROM
  table1
  ,table2
  ,table3
WHERE
  <join conditions>
;

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

The Oracle syntax is still supported in Oracle version after 9i for backward compatibility. However, the ANSI syntax has several advantages compared to the Oracle syntax:

it is easier to find the join condition used for any pair of tables (no need to dig through a large WHERE clause) and in general separate the filter conditions from join conditions

as a consequence it is less likely that one forgets a join condition causing a cartesian join

outer joins become more explicit (ANSI uses the keyword OUTER instead of the (+) used in Oracle syntax next to the column name in the join condition)

ANSI JOIN syntax

The ANSI standard provides following syntax for defining joins (to be used in place of <JOIN DEFINITION> in the above code template):

CROSS JOIN
The join combines each row in the left table with all rows in the right table and returns all row combinations.

INNER JOIN (equivalent to JOIN)
Combines rows from the left table with rows in the right table using the join condition. Only rows matching the join condition are returned.

LEFT OUTER JOIN (equivalent to LEFT JOIN)
Combines rows from the left table with rows in the right table using the join condition. In addition to all rows matching the join condition, all rows from the left table with no match are returned.

RIGHT OUTER JOIN (equivalent to RIGHT JOIN)
Combines rows from the left table with rows in the right table using the join condition. In addition to all rows matching the join condition, all rows from the right table with no match are returned.

FULL OUTER JOIN (equivalent to FULL JOIN)
Combines rows from the left table with rows in the right table using the join condition. In addition to all rows matching the join condition, all rows from both the right and the left table with no match are returned.

In addition the ANSI standards allows the prefix NATURAL to be used for the inner join and the three outer joins:
NATURAL INNER JOIN (or NATURAL JOIN)
NATURAL LEFT OUTER JOIN (or NATURAL JOIN)
NATURAL RIGHT OUTER JOIN (or NATURAL RIGHT JOIN)
NATURAL FULL OUTER JOIN or (NATURAL FULL JOIN)

The use of NATURAL defines that the two tables are joined using all identically named columns appearing in both tables. When NATURAL is used the ON clause must not be specified. The output of a NATURAL join includes the identical columns (used for the join condition) only once.

The join condition can be defined in two ways. The more common one uses the ON syntax, e.g.:

SELECT
  lt.col_1
  rt.col_2
FROM
  left_table
INNER JOIN
  right_table
ON
  lt.join_col = rt.join_col
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select all Open in new window

When the columns have identical names in both tables, the alternative USING syntax may be used. The following is equivalent to the previous example:

SELECT
  lt.col_1
  rt.col_2
FROM
  left_table
INNER JOIN
  right_table
USING(join_col)
;

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

Oracle fully supports all of the ANSI JOIN syntax defined above.

Join type descriptions

The above syntax defines how joins can be specified in SQL. The syntax can be used to implement various join types which have been given descriptive labels. The following gives an overview of such descriptive labels and how they can be implemented in ANSI SQL. The labels are not in all cases mutually exclusive. E.g. a theta-join can also be a self-join.

Cross join

Also known as a cartesian product or cartesian join. Combines all rows in the left table with all rows in the right table. Implemented in SQL using CROSS JOIN.

Theta-join

Combines rows from the left table with rows in the right table using the join condition and returns only rows matching the join condition. Implemented in SQL using INNER JOIN.

Equi-join

A special type of Theta-join where only equality operators are allowed in the join condition.

Outer join

An outer join is equivalent to a theta-join with non-matching rows added to the result set from either or both tables. Implemented in SQL using FULL / LEFT / RIGHT OUTER JOIN.

Semi-join

Return rows only from the left table (each row at most once regardless of number of matches in right table).

Usually implemented using IN or EXISTS

E.g.:

SELECT
  t1.*
FROM
  table_1 t1
WHERE
  t1.some_column IN (SELECT some_other_column FROM table_2)
; 

SELECT
  t1.*
FROM
  table_1 t1
WHERE
  EXISTS (SELECT some_other_column FROM table_2 t2 WHERE t2.some_other_column = t1.some_column)
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all Open in new window

Anti-join

A special type of semi-join where only the rows from the left table are returned that have no match if the right table. Usually implemented using NOT IN or NOT EXISTS

E.g.:

SELECT
  t1.*
FROM
  table_1 t1
WHERE
  t1.some_column NOT IN (SELECT some_other_column FROM table_2)
; 

SELECT
  t1.*
FROM
  table_1 t1
WHERE
  NOT EXISTS (SELECT some_other_column FROM table_2 t2 WHERE t2.some_other_column = t1.some_column)
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all Open in new window

SELECT
  *
FROM
  t1
FULL OUTER JOIN
  t2
ON
  1 = 2
;

1:
2:
3:
4:
5:
6:
7:
8:
9:

Select all Open in new window

Join types and their implementation using ANSI JOIN syntax in Oracle

Oracle

Join

ANSI

Oracle Database

About The Author

More Articles From tosse

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Oracle Database Experts