Database Reference
In-Depth Information
Types of Joins
There are two syntax types for specifying joins. The legacy syntax, which was specified by the very first SQL standard
(SQL-86), uses both the
FROM
clause and the
WHERE
clause to specify joins. The newer syntax, available for the first
time in SQL-92, uses only the
FROM
clause to specify a join. The newer syntax is sometimes called
ANSI join syntax
.
However, both syntax types are valid from a SQL standard point of view. With Oracle Database, for historical reasons,
the most commonly used syntax is the legacy one. In fact, not only are many developers and DBAs used to it, but
many applications were developed using it as well. Nevertheless, the newer syntax offers possibilities that the legacy
syntax doesn't support. The following sections provide examples based on both syntaxes. All queries used here as
examples are provided in the
join_types.sql
script.
■
The join types described in this section aren't mutually exclusive. a given join may fall into more than one
category. For example, it's perfectly plausible to conceive a theta join that is also a self-join.
Note
Cross Joins
A
cross join
, also called
Cartesian product
, is the operation that combines every row of one table with every row of
another table. This type of operation is carried out in the two situations illustrated with the following queries. The first
uses the legacy join syntax (no join condition is specified):
SELECT emp.ename, dept.dname FROM emp, dept
The second uses the new join syntax (the
CROSS JOIN
is used):
SELECT emp.ename, dept.dname FROM emp CROSS JOIN dept
In reality, cross joins are rarely needed. Nevertheless, the latter syntax is better to document the developer's
intention. In fact, it has the advantage of being explicitly specified. With the former, it's not clear whether the person
who wrote the SQL statement forgot a
WHERE
clause.
Theta Joins
A
theta join
is equivalent to performing a selection over the result set of a cross join. In other words, instead of
returning a combination of every row from one table with every row from another table, only the rows satisfying a join
condition are returned. The following two queries are examples of this type of join:
SELECT emp.ename, salgrade.grade
FROM emp, salgrade
WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal
SELECT emp.ename, salgrade.grade
FROM emp JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal
Theta joins are also called
inner joins
. In the previous query using the new join syntax, the keyword
INNER
was
assumed, but it can be explicitly coded, as in the following example:
