Sybase 12.4.2 manual 163

CHAPTER 4 Adaptive Server IQ Indexes

Example 1: Key join

Example 2: ON clause join

Example 3: Natural join

search-condition:

[ ( ] search-expression [ AND search-expression ] [ ) ]

•The join-clausecan be expressed either with or without parentheses.

•The ON clause can reference only two tables. One must be the current one, and the other can be any one table in the current join tree.

•All join predicates must be equijoins; that is, the search_expression must indicate that the value in column_1 equals the value in column_2. No single-variable predicates, intracolumn comparisons, or non-equality joins are permitted in the ON clause.

•To specify a multicolumn join, you include more than one predicate linking the two tables, and connect them with logical AND.

•You cannot connect join predicates with logical OR.

•The keyword NATURAL can replace the ON clause, when you are pairing columns from a single pair of tables by name.

Here is an example of how you create a join index for the key join between the sales_order table and the customer table. Remember that this is a key join, based on the foreign key ky_so_customer which relates the cust_id column of sales_order to the primary key id of the customer table. You can give the index any name you want. This example names it ky_so_customer_join to identify the foreign key on which the key join relies.

CREATE JOIN INDEX ky_so_customer_join

FOR customer FULL OUTER JOIN sales_order

The next example shows how you could create a join index for the same two tables using an ON clause. You could use this syntax whether or not the foreign key existed.

CREATE JOIN INDEX customer_sales_order_join

FOR customer FULL OUTER JOIN sales_order

ON customer_id=sales_order.cust_id

To create a natural join, the joined columns must have the same name. If you created a natural join on the tables in previous examples, you would not get the expected results at all. Instead of joining the id column of customer to the cust_id column of sales_order, the following command would join the dissimilar id columns of the two tables:

CREATE JOIN INDEX customer_sales_order_join

FOR customer NATURAL FULL OUTER JOIN sales_order

163