ASA Getting Started
  Selecting Data from Multiple Tables

Joining tables using key joins

Many common joins are between two tables related by a foreign key. The most common join restricts foreign key values to be equal to primary key values.

The KEY JOIN operator joins two tables based on foreign key relationship. In other words, Adaptive Server Anywhere generates an ON clause that equates the primary key column from one table with the foreign key column of the other.

The example in the previous section restricts foreign key values in the sales_order table to be equal to the primary key values in the employee table.

SELECT employee.emp_lname,
       sales_order.id,
       sales_order.order_date
FROM sales_order JOIN employee
   ON sales_order.sales_rep = employee.emp_id

The query can be more simply expressed using a KEY JOIN:

SELECT employee.emp_lname,
       sales_order.id,
       sales_order.order_date
FROM sales_order KEY JOIN employee

KEY JOIN is just a shortcut for typing the ON clause; the two queries are identical. Key join is the default when you specify JOIN, but do not specify CROSS, NATURAL, KEY, or use an ON clause.

If you look at the diagram of the employee database, lines between tables represent foreign keys. You can use the KEY JOIN operator anywhere two tables are joined by a line in the diagram.

For a diagram of the sample database, see The sample database.

Joining more than two tables 

Two or more tables can be joined using join operators. The following query uses four tables to list the total value of the orders placed by each customer. It connects the four tables customer, sales_order, sales_order_items, and product using the single foreign-key relationships between each pair of these tables.

List companies and the total value of their orders

• In Interactive SQL, type the following in the SQL Statements pane and press F5 to execute the statement.

  SELECT company_name,
         SUM( sales_order_items.quantity *
              product.unit_price) AS value
  FROM ( ( customer KEY JOIN sales_order )
     KEY JOIN sales_order_items )
     KEY JOIN product
  GROUP BY company_name

  company_name	value
  Bensoul's Boutique	1332
  Bush Pro Shop	2940
  Sterling & Co.	6804
  Ocean Sports	3744
  ...	...

Notes 

Your result set may appear in a different order. There is no significance to the order of the rows in the result set.

The example uses the SUM operator, which is an aggregate function. Aggregate functions work with GROUP BY clauses to return values for each row group. In this example, the sum of sales_order_items.quantity * product.unit_price—that is, the total amount of money paid per product type—is calculated for each company_name, thereby returning each company's sales.

The parentheses in the FROM clause help to clarify the order in which the joins are made.

For more information on aggregate functions, see A first look at aggregate functions.

For more information on complex key joins, see Key joins.