Background documentationSearch Strategies for Joins Locate this document in the navigation structure

 

A join is an SQL statement that links several tables and returns a join table as its result.

To perform a join as quickly as possible, the SQL optimizer searches for the best sequence when linking the tables. The aim is to access the rows of the tables that are to be linked as directly as possible, and to keep the size of the intermediate result tables to a minimum. The database system therefore starts the join with the smallest tables for which the most restrictive search conditions have been specified. This initially keeps the temporary results tables small.

Note Note

The order in which the tables were specified in the FROM clause of the SELECT statement has no influence on the order of their processing.

End of the note.
Search Strategies for Joins

The database system 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.

Note that only if the two columns that you want to link in a join step have the same column length, the SQL optimizer can use all of the search strategies mentioned above. Define the same value range (domain) for all columns you want to join.

Example

The following example uses the demo database DEMODB with the complete demo data in the schema HOTEL, see Concepts of the Database System, Objects in the Schema HOTEL.

JOIN Statement

Variant 1

EXPLAIN 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

Variant 2

EXPLAIN SELECT customer.cno, customer.title, customer.name, customer.zip, city.name, city.state, customer.address

  FROM hotel.customer

    INNER JOIN hotel.city ON customer.zip = city.zip

Description:

  • Join tables: CUSTOMER and CITY

  • Qualification: customer.zip = city.zip

  • Search strategy for CITY: TABLE SCAN

  • Search strategy 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 database system first accesses the CITY table using a TABLE SCAN. For joining this table with the table CUSTOMER, the system chooses the JOIN VIA KEY RANGE strategy using the ZIP column. For the join, the database system always creates a result set (RESULT IS COPIED).

More Information

You can find additional simple example SQL statements in the SQL Tutorial, Joins: Information From Several Tables.