Multiple-table SELECT statements

To select data from two or more tables, specify the table names in the FROM clause. Add a WHERE clause to create a join condition between at least one related column in each table. This WHERE clause creates a temporary composite table in which each pair of rows that satisfies the join condition is linked to form a single row.

A simple join combines information from two or more tables based on the relationship between one column in each table. A composite join is a join between two or more tables based on the relationship between two or more columns in each table.

To create a join, you must specify a relationship, called a join condition, between at least one column from each table. Because the columns are being compared, they must have compatible data types. When you join large tables, performance improves when you index the columns in the join condition.

Data types are described in the Informix® Guide to SQL: Reference and the Informix® Database Design and Implementation Guide. Indexing is discussed in detail in the Informix® Administrator's Guide.