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 INDEXED COLUMN

JOIN VIA KEY COLUMN

JOIN VIA KEY RANGE

JOIN VIA MULTIPLE INDEXED COLUMNS

JOIN VIA MULTIPLE KEY 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
COSTVALUE IS


13

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

EXPLAIN Statement for Joins

SQL Reference Manual, Domain Name (domain_name)