Joins 
A join is an SQL statement that links several tables with each other and returns a join table as its result.
See also:
SQL Tutorial, 
Joins: Information
from Several Tables
SQL Reference Manual, JOIN Predicate (join_predicate)
Features
The SQL Optimizer's task is to find the optimal sequence for processing the tables. The SQL Optimizer tries to access the rows of the new tables to be linked as directly as possible and to keep the size of the intermediate result tables to a minimum. For this reason, it starts by processing joins for the smallest tables with the most restrictive search conditions. Initially, this keeps the temporary results tables small.
The order in which the tables are specified in the FROM clause of the SELECT statement has no influence on the order of their processing.
Search Strategies for Joins
The SQL Optimizer can choose one of the following search strategies to access rows in the new table, starting from the join column values of the old temporary results table.
JOIN VIA MULTIPLE INDEXED COLUMNS
JOIN VIA RANGE OF MULTIPLE INDEXED COL.
JOIN VIA RANGE OF MULTIPLE KEY COLUMNS
If the two columns that you want to compare in a join step do not have the same column length, the SQL Optimizer cannot use all of the abovementioned search strategies. To avoid this restriction, define the same value range (domain) for any columns that you want to join together.
Examples
The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.
Concepts of the Database System, Objects in the Schema HOTEL
SELECT customer.cno, customer.title,
customer.name,
customer.zip, city.name, city.state,
customer.address
FROM hotel.customer, hotel.city
WHERE customer.zip = city.zip
Join tables:
CUSTOMER and CITY
Qualification: customer.zip = city.zip
Search strategy used for
CITY: TABLE
SCAN
Search strategy used for
CUSTOMER: JOIN
VIA KEY RANGE
Result of the EXPLAIN Statement
TABLENAME |
COLUMN_OR_INDEX |
STRATEGY |
PAGECOUNT |
CITY |
|
TABLE SCAN |
1 |
CUSTOMER |
|
JOIN VIA KEY RANGE |
1 |
|
|
TABLE TEMPORARY SORTED |
|
|
ZIP |
(USED SORT COLUMN) |
|
|
|
RESULT IS COPIED |
|
The join processing begins with the access to the table CITY. This is performed with the search strategy TABLE SCAN.
For the join transfer to the table CUSTOMER with the strategy JOIN VIA KEY RANGE, the column ZIP is used.
For the join, a result set is always generated (RESULT IS COPIED).
See also:
Database parameter JOIN_SEARCH_LEVEL
SQL Reference Manual, Domain Name (domain_name)