Function documentationJoins Locate this document in the navigation structure

 

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

Features

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 new tables to be linked as directly as possible and to keep the size of the intermediate result tables to a minimum. The database system therefore begins joins with the smallest tables with the most restrictive search conditions. Initially, this keeps the temporary results tables small.

Note Note

The order in which the tables have been 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 Note

Unless the two columns that you want to compare in a join step have the same column length, the SQL Optimizer cannot use all of the search strategies mentioned above. To avoid this restriction, define the same value range for all columns you want to join together (“domains”).

End of the note.

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

Example Example

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

  • Join tables: CUSTOMER and CITY

  • Qualification: customer.zip = city.zip

  • Search strategy for CITY: TABLE SCAN

  • Search strategy for CUSTOMER: JOIN VIA KEY RANGE

End of the example.

The database system accesses the CITY table. For this it uses the TABLE SCAN strategy. For the join with the table CUSTOMER used with the JOIN VIA KEY RANGE strategy, the database system uses the ZIP column. For the join, the database system always creates up a hit list (RESULT IS COPIED).

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

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

More Information