Sybase 12.4.2 - Manual (Page 183)

CHAPTER 4 Adaptive Server IQ Indexes
163
search-condition:
[ ( ]
search-expression [ AND search-expression ] [ ) ]
• The join-clause can 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.
Example 1: Key join 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
Example 2: ON clause
join 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
Example 3: Natural
join 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