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:

SYNTAX
{ FROM table_ref CROSS JOIN table_ref
  | FROM table_ref [ INNER ] JOIN table_ref ON search_condition
} 

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_condition

Specify 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 the WHERE form of inner joins and the JOIN ON form.

This is the syntax for a where_clause_inner_join:

SYNTAX
FROM table_ref, table_ref WHERE search_condition 

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 WHERE 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.

EXAMPLE

The following queries illustrate the results of a simple CROSS JOIN operation and an equivalent formulation that does not use the CROSS JOIN syntax:

SELECT * FROM T1; -- Contents of T1
     C1      C2
     --      --
     10      15
     20      25
2 records selected
SELECT * FROM T2; -- Contents of T2
     C3 C4 
     -- --  
     10 BB 
     15 DD 
2 records selected
SELECT * FROM T1 CROSS JOIN T2; -- Cartesian product
     C1      C2      C3 C4 
     --      --      -- --  
     10      15      10 BB 
     10      15      15 DD 
     20      25      10 BB 
     20      25      15 DD 
4 records selected
SELECT * FROM T1, T2; -- Different formulation, same results
     C1      C2      C3 C4 
     --      --      -- --  
     10      15      10 BB 
     10      15      15 DD 
     20      25      10 BB 
     20      25      15 DD 
4 records selected 

For customers with orders, retrieve their names and order info:

SELECT customer.cust_no, customer.name, orders.order_no, orders.order_date
     FROM customers, orders
     WHERE customer.cust_no = orders.cust_no ; 

This example illustrates two ways to formulate a self-join query:

-- Retrieve all the customers from the same city as customer SMITH:
SELECT y.cust_no, y.name
     FROM customer AS x INNER JOIN customer AS y
     ON x.name = ’SMITH’ AND y.city = x.city ;
 
-- Different formulation, same results:
SELECT y.cust_no, y.name
     FROM customer x, customer y
     WHERE x.name = ’SMITH’ AND y.city = x.city ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095