Join types and their implementation using ANSI JOIN syntax in Oracle

Published:
Introduction

A previously published article on Experts Exchange ("Joins in Oracle", https://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>
                      ;

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

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

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
                      ;

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

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

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

Open in new window

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

Open in new window

1
6,804 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.