Progress
SQL-92
Guide and Reference
Inner Joins
If an inner join does not specify a search condition, the result table from the join operation is the Cartesian product of rows in the tables, formed by concatenating every row of one table with every row of the other table. Cartesian products (also called cross products or cross joins) are not practically useful, but SQL logically processes all join operations by first forming the Cartesian products of rows from tables participating in the join.
If specified, the search condition is applied to the Cartesian product of rows from the two tables. Only rows that satisfy the search condition become part of the result table generated by the join.
A query expression can specify inner joins in either its FROM clause or its WHERE clause. For each formulation in the FROM clause, there is an equivalent syntax formulation in the WHERE clause. Currently, not all syntax specified by the SQL-92 standard is allowed in the FROM clause.
This is the syntax for a from_clause_inner_join:
FROM table_ref CROSS JOIN table_ref
Explicitly specifies that the join generates the Cartesian product of rows in the two table references. This syntax is equivalent to omitting the
WHERE
clause and a search condition.FROM table_ref [ INNER ] JOIN table_ref ON search_condition
FROM table_ref, table_ref WHERE search_conditionSpecify search_condition for restricting rows that will be in the result table generated by the join. In the first format,
INNER
is optional and has no effect. There is no difference between theWHERE
form of inner joins and theJOIN ON
form.This is the syntax for a
where_clause_inner_join
:
Equi-joins
Specifies that values in one table equal the corresponding column values in the other.
Self joins
Joins a table with itself. If a
EXAMPLEWHERE
clause specifies a self join, the FROM clause must use aliases to allow two different references to the same table. Also called an auto join.The following queries illustrate the results of a simple CROSS JOIN operation and an equivalent formulation that does not use the CROSS JOIN syntax:
For customers with orders, retrieve their names and order info:
This example illustrates two ways to formulate a self-join query:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |