Issuing the CREATE JOIN INDEX Statement

The following examples illustrate how to issue CREATE JOIN INDEX statements.

Example 1: Key Join

This example creates a join index for the key join between the SalesOrders table and the Customers table. This is a key join based on the foreign key ky_so_customer, which relates the CustomerID column of SalesOrders to the primary key ID of the Customers 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 GROUPO.Customers FULL OUTER JOIN GROUPO.SalesOrders

Example 2: ON Clause Join

This example creates 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 GROUPO.Customers FULL OUTER JOIN GROUPO.SalesOrders
ON Customers.ID=SalesOrders.CustomerID

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 Customers to the CustomerID column of SalesOrders, the following command would join the dissimilar ID columns of the two tables, which is not allowed:

CREATE JOIN INDEX customers_sales_order_join 
FOR GROUPO.Customers NATURAL FULL OUTER JOIN GROUPO.SalesOrders

A natural join between the id columns of SalesOrders and SalesOrderItems makes more sense. In this case, the columns with the same name should contain matching values. The command to create a join index based on a natural join between these two tables is:

CREATE JOIN INDEX sales_order_so_items_join
FOR GROUPO.SalesOrders NATURAL FULL OUTER JOIN GROUPO.SalesOrderItems