SQL-J Language Reference
Page 54 of 121

TableExpression

{
    { TableOrViewExpression | VirtualTableExpression } |
     JOIN operation
}

TableOrViewExpression

{ TableName | ViewName }
    [ [ AS ] CorrelationName
        [ ( SimpleColumnName [ , SimpleColumnName]* ) ] ] ]
[ PROPERTIES clause ]

VirtualTableExpression

TableSubquery | ExternalVirtualTable |
    [ AS ] CorrelationName
        [ ( SimpleColumnName [ , SimpleColumnName]* ) ]
[ PROPERTIES clause ]

A TableExpression specifies a real table or view or a virtual table in a FROM clause. It is the source from which a SelectExpression selects a result. Virtual tables that are not ExternalVirtualTables (TableSubqueries, including VALUES expressions) are delimited by parentheses (see VALUES expression and TableSubquery). All virtual tables including ExternalVirtualTables must have correlation names.

It also has an optional PROPERTIES clause that allows you to override the optimizer's choice of access path, join strategy, and I/O size (bulk fetch size).

A correlation name can be applied to a table in a TableExpression so that its columns can be qualified with that name. If you do not supply a correlation name, the table name qualifies the column name. When you give a table a correlation name, you cannot use the table name to qualify columns. You must use the correlation name when qualifying column names.

No two items in the FROM clause can have the same correlation name, and no correlation name can be the same as an unqualified table name specified in that FROM clause.

In addition, you can give the columns of the table new names in the AS clause. Some situations in which this is useful:

  • When a VALUES expression is used as a TableSubquery, since there is no other way to name the columns of a VALUES expression.
  • When column names would otherwise be the same as those of columns in other tables; renaming them means you don't have to qualify them.
  • To make its name match that of a column in another table so that you can use a JOIN operation with the USING clause.

The Query in a TableSubquery appearing in a FromItem can contain multiple columns and return multiple rows. See TableSubquery.

The PROPERTIES clause allows you to override the optimizer. Legal properties are:

For information about the optimizer overrides you can specify, see Tuning Cloudscape.

With the exception of joinStrategy, properties can be specified only on base tables and are not allowed on views or TableExpressions in a FROM clause (derived tables). They are allowed on any base table within a FROM clause or any table within a FROM clause in a subquery. They can be used on the base tables specified in a CREATE VIEW statement, within a derived table (subquery in the FROM list), and within a JOIN clause. Since most error checking for the PROPERTIES clause is done during query optimization, errors for PROPERTIES clauses in a CREATE VIEW statement are not returned until the view is first used in a SELECT statement.

TableExpression Examples

-- SELECT from a table subquery that has a correlation name
-- (a derived table)
SELECT VirtualFlightTable.flight_number
FROM (SELECT flight_ID, orig_airport, dest_airport
    FROM Flights WHERE orig_airport = 'SFO'
    OR dest_airport = 'SCL' )
AS VirtualFlightTable (flight_number, airport1, airport2)

-- SELECT from a Join expression
SELECT Cities.city_id, Hotels.hotel_id
FROM Cities JOIN Hotels USING (city_id)
WHERE Cities.city_id < 20

-- SELECT from an ExternalVirtualTable
-- you must give ExternalVirtualTables correlation names
SELECT *
FROM NEW ExternalQuery(
    'jdbc:cloudscape:History', 'SELECT * FROM HotelBookings')
AS EQ

-- force use of the primary key constraint
-- instead of another index that includes
-- the flight_id column
SELECT *
FROM Flights
PROPERTIES constraint= Flights_pk
WHERE flight_id LIKE 'AA111%'

-- force a hash join
SELECT a.name FROM NEW jarvti('lib/cloudscape.jar') AS a,
    NEW jarvti('lib/tools.jar') AS b
        PROPERTIES joinStrategy=hash
WHERE a.name=b.name