|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Queries and Subqueries, 7 of 11
A join is a query that combines rows from two or more tables, views, or materialized views. Oracle performs a join whenever multiple tables appear in the query's
FROM clause. The query's select list can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.
Most join queries contain
WHERE clause conditions that compare two columns, each from a different table. Such a condition is called a join condition. To execute a join, Oracle combines pairs of rows, each containing one row from each table, for which the join condition evaluates to
TRUE. The columns in the join conditions need not also appear in the select list.
To execute a join of three or more tables, Oracle first joins two of the tables based on the join conditions comparing their columns and then joins the result to another table based on join conditions containing columns of the joined tables and the new table. Oracle continues this process until all tables are joined into the result. The optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables.
In addition to join conditions, the
WHERE clause of a join query can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.
You cannot specify LOB columns in the
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. Depending on the internal algorithm the optimizer chooses to execute the join, the total size of the columns in the equijoin condition in a single table may be limited to the size of a data block minus some overhead. The size of a data block is specified by the initialization parameter
A self join is a join of a table to itself. This table appears twice in the
FROM clause and is followed by table aliases that qualify column names in the join condition. To perform a self join, Oracle combines and returns rows of the table that satisfy the join condition.
If two tables in a join query have no join condition, then Oracle returns their Cartesian product. Oracle combines each row of one table with each row of the other. A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows. Always include a join condition unless you specifically need a Cartesian product. If a query joins three or more tables and you do not specify a join condition for a specific pair, then the optimizer may choose a join order that avoids producing an intermediate Cartesian product.
An inner join (sometimes called a "simple join") is a join of two or more tables that returns only those rows that satisfy the join condition.
An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
JOINsyntax in the
FROMclause, or apply the outer join operator (+) to all columns of B in the join condition in the
WHEREclause. For all rows in A that have no matching rows in B, Oracle returns null for any select list expressions containing columns of B.
JOINsyntax in the
FROMclause, or apply the outer join operator (+) to all columns of A in the join condition in the
WHEREclause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A.
JOINsyntax in the
Oracle Corporation recommends that you use the
JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the
FROM clause join syntax:
FROMclause join syntax.
WHEREclause or, in the context of left-correlation (that is, when specifying the
TABLEclause) in the
FROMclause, and can be applied only to a column of a table or view.
-- The following statement is not valid: SELECT employee_id, manager_id FROM employees WHERE employees.manager_id(+) = employees.employee_id;
However, the following self join is valid:
SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id;
WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column. Otherwise Oracle will return only the results of a simple join.
In a query that performs outer joins of more than two pairs of tables, a single table can be the null-generated table for only one other table. For this reason, you cannot apply the (+) operator to columns of B in the join condition for A and B and the join condition for B and C.
SELECT for the syntax for an outer join