Joining Tables (Embedded SQL Guide)
Go Up to Working with Data
Joins enable retrieval of data from two or more tables in a database with a single SELECT. The tables from which data is to be extracted are listed in the FROM clause. Optional syntax in the FROM clause can reduce the number of rows returned, and additional WHERE clause syntax can further reduce the number of rows returned.
From the information in a SELECT that describes a join, InterBase builds a table that contains the results of the join operation, the results table, sometimes also called a dynamic or virtual table.
InterBase supports two types of joins:
- Inner joins link rows in tables based on specified join conditions, and return only those rows that match the join conditions. There are three types of inner joins:
- Equi-joins link rows based on common values or equality relationships in the join columns.
- Joins that link rows based on comparisons other than equality in the join columns. There is not an officially recognized name for these types of joins, but for the sake of simplicity they may be categorized as comparative joins, or non-equi-joins.
- Reflexive or self-joins, compare values within a column of a single table.
- Outer joins link rows in tables based on specified join conditions and return both rows that match the join conditions, and all other rows from one or more tables even if they do not match the join condition.
The most commonly used joins are inner joins, because they both restrict the data returned, and show a clear relationship between two or more tables. Outer joins, however, are useful for viewing joined rows against a background of rows that do not meet the join conditions.