<< Click to Display Table of Contents >> Navigation:  Local SQL > Data Manipulation Language > Relational operators > INNER JOIN

Joins two tables based on column values common between the two, excluding non-matches.

SELECT column_list

FROM table_reference

 [INNER] JOIN table_reference

   ON predicate

 [[INNER] JOIN table_reference

   ON predicate...]

Description

Use an INNER JOIN to join two tables, a source and joining table, that have values from one or more columns in common. One or more columns from each table are compared in the ON clause for equal values. For rows in the source table that have a match in the joining table, the data for the source table rows and matching joining table rows are included in the result set. Rows in the source table without matches in the joining table are excluded from the joined result set. In the statement below, the CUSTOMER and ORDERS tables are joined based on values in the CUSTNO column, which each table contains.

SELECT *

FROM Customer C

 INNER JOIN Orders O

   ON (C.CustNo = O.CustNo)

More than one table may be joined with an INNER JOIN. One use of the INNER JOIN operator and corresponding ON clause is required for each each set of two tables joined. One columns comparison predicate in an ON clause is required for each column compared to join each two tables. The statement below joins the CUSTOMER table to ORDERS, and then ORDERS to ITEMS. In this case, the joining table ORDERS acts as a source table for the joining table ITEMS. (The statement below appears without the optional INNER keyword.)

SELECT *

FROM Customer C

 JOIN Orders O

   ON (C.CustNo = O.CustNo)

 JOIN items I

   ON (O.OrderNo = I.OrderNo)

Tables may also be joined using a concatenation of multiple column values to produce a single value for the join comparison predicate. Here, the ID1 and ID2 columns in JOINING are concatenated and compared with the values in the single column ID in SOURCE.

SELECT *

FROM Source S

 INNER JOIN Joining J

   ON (S.ID = J.ID1 || J.ID2)

An ORDER BY clause in INNER JOIN statements can use columns from any table specified in the FROM clause to sort the result set.