SQL-J Language Reference
Page 36 of 121

INNER JOIN

An INNER JOIN is a JOIN operation that allows you to specify an explicit join clause.

Syntax

TableExpression [ INNER ] JOIN TableExpression
{
    ON booleanExpression |
    USING ( SimpleColumnName [ , SimpleColumnName]* )
}

You can specify the join clause in one of two ways:

  • Specifying ON with a boolean expression.
  • Specifying USING (column list), where every column named in the column list appears in both tables. Cloudscape performs an equijoin between the two tables using the columns named in the column list.

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 *
FROM Countries, Cities
WHERE Countries.country_ISO_code = Cities.country_ISO_code

SELECT *
FROM Countries INNER JOIN Cities
USING (country_ISO_code)

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 *
FROM Hotels INNER JOIN Cities
ON (Hotels.city_id = Cities.city_id)

In the following example, the ON clause appears in a subquery and refers to columns in the outer query:

SELECT *
FROM t1
WHERE x IN
(SELECT y FROM t2 INNER JOIN t3 ON z = t1.a)

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
SELECT *
FROM Flights JOIN FlightAvailability
USING (flight_id, segment_number)
WHERE orig_airport = 'SFO'

-- Join Cities with Countries, use a correlation name for
-- a method invocation
SELECT DISTINCT city.getLanguage() AS Lang, country
FROM Cities JOIN Countries
USING (country_ISO_code)
ORDER BY Lang

-- Join two values clauses
SELECT *
FROM (VALUES (3, 4), (1, 5), (2, 6))
AS valuesTable1(c1, c2)
JOIN (VALUES (3, 2), (1, 2),
    (0, 3)) AS valuesTable2(c1, c2)
ON valuesTable1.c1 = valuesTable2.c1

C1 |C2 |C1 |C2
----------------------------------------------------------
3 |4 |3 |2
1 |5 |1 |2

-- Join Airlines with a view
SELECT airline_full, flight_id, number_seats
FROM Airlines
JOIN Segments_SeatBookings ON     Segments_SeatBookings.flight_id.substring(0,2).equals(
    airline)

-- a TableExpression can be a joinOperation. Therefore
-- you can have multiple join operations in a FROM clause
SELECT country, city.getName(), hotel_name, normal_rate
FROM COUNTRIES INNER JOIN Cities
ON Countries.country_ISO_code=Cities.country_ISO_code
INNER JOIN Hotels
ON Cities.city_id=Hotels.city_id

ResultSet and Performance Differences Between ON and USING

In accordance with the ANSI standard, join operations that specify USING return results different from those that specify ON. Consider the following examples:

SELECT *
FROM Countries INNER JOIN Cities
ON Countries.country_ISO_code = Cities.country_ISO_code

SELECT *
FROM Countries INNER JOIN Cities
USING (country_ISO_code)

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.