Filter
Top Products
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