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.
