![]() |
SQL-J Language Reference
|
Reference Manual |
INNER JOINAn INNER JOIN is a JOIN operation that allows you to specify an explicit join clause. Syntax
TableExpression [ INNER ] JOIN TableExpression You can specify the join clause in one of two ways:
For additional differences between ON and USING clauses, see ResultSet and Performance Differences Between ON and USING. The INNER JOIN operation produces the same results as a query with an equivalent WHERE clause. The following two examples are equivalent:
SELECT *
SELECT * The scope of expressions in either the ON or the USING clause includes the current tables and any tables in outer query blocks to the current SELECT. In the following example, the ON clause refers to the current tables:
SELECT * In the following example, the ON clause appears in a subquery and refers to columns in the outer query: SELECT * The ON clause can reference tables not being joined and does not have to reference either of the tables being joined (though typically it does). Example
-- join the Flights and FlightAvailability tables
-- Join Cities with Countries, use a correlation name for
-- Join two values clauses C1 |C2 |C1 |C2
-- Join Airlines with a view
-- a TableExpression can be a joinOperation. Therefore ResultSet and Performance Differences Between ON and USINGIn accordance with the ANSI standard, join operations that specify USING return results different from those that specify ON. Consider the following examples:
SELECT *
SELECT * The first query returns all the columns in the first table (country, country_ISO_code, and region), plus all the columns in the second table (city_id, city, and country_ISO_code). Note that the join column, country_ISO_code, appears twice in the ResultSet. For the second query, Cloudscape strips out the join column before returning the result and puts the join column first in the result. The query returns all the columns in the first table (with the join column first), plus all the columns in the second table minus the join column. Thus, the query returns country, country_ISO_code, region, city_id, and city. Using a select list other than SELECT * allows you to customize your ResultSet. NOTE: Joins with the USING clause have a slightly slower performance than those using ON or those using simple WHERE clauses, because Cloudscape must perform an additional restriction operation to return the final result. For performance reasons, avoid joins with USING clauses. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |