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
Summary of Contents for Adaptive Server IQ 12.4.2
Page 1: ...Administration and Performance Guide Adaptive Server IQ 12 4 2 ...
Page 16: ...xvi ...
Page 20: ...Related documents xx ...
Page 40: ...Compatibility with earlier versions 20 ...
Page 118: ...Troubleshooting startup shutdown and connections 98 ...
Page 248: ...Importing data by replication 228 ...
Page 306: ...Integrity rules in the system tables 286 ...
Page 334: ...Cursors in transactions 314 ...
Page 396: ...Users and permissions in the system tables 376 ...
Page 438: ...Determining your data backup and recovery strategy 418 ...
Page 484: ...Network performance 464 ...
Page 500: ...System utilities to monitor CPU use 480 ...
Page 514: ...Characteristics of Open Client and jConnect connections 494 ...
Page 536: ...Index 516 ...
