First Guide to SQL Anywhere Studio
   PART 3. Basic SQL
     CHAPTER 13. Joining Tables       

Join operators

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 example you have already seen restricts foreign key values in the sales_order table to be equal to the primary key values in the employee table.

SELECT emp_lname, id, 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.

  Joining tables using key joins

Key joins are the best way to join two tables related by a single foreign key. For example,

SELECT emp_lname, id, order_date
FROM sales_order
   KEY JOIN employee

gives the same results as a query with a ON phrase that equates the two employee number columns:

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

The join operator KEY JOIN is just a short cut for typing the ON phrase; the two queries are identical.

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

Joining two or more 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.

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

company_name	value
Able Inc.	6120
AMF Corp.	3624
Amo & Sons	3216
Amy's Silk Screening	2028
Avco Ent	1752
...	...

The CAST function used in this query converts the data type of an expression. In this example the sum that is returned as an integer is converted to a value.

  Joining tables using natural joins

The NATURAL JOIN operator joins two tables based on common column names. In other words, Adaptive Server Anywhere generates a ON phrase that equates the common columns from each table.

Example 

For example, for the following query:

SELECT emp_lname, dept_name
FROM employee
   NATURAL JOIN department

the database engine looks at the two tables and determines that the only column name they have in common is dept_id. The following ON phrase is internally generated and used to perform the join:

FROM employee JOIN department
   ON employee.dept_id = department.dept_id

Errors using NATURAL JOIN 

This join operator can cause problems by equating columns you may not intend to be equated. For example, the following query generates unwanted results:

SELECT *
FROM sales_order
NATURAL JOIN customer

The result of this query has no rows.

The database engine internally generates the following ON phrase:

FROM sales_order JOIN customer
   ON sales_order.id = customer.id

The id column in the sales_order table is an ID number for the order. The ID column in the customer table is an ID number for the customer. None of them matched. Of course, even if a match were found, it would be a meaningless one.

You should be careful not to use join operators blindly. Always remember that the join operator just saves you from typing the ON phrase for a foreign key or common column names. You should be conscious of the ON phrase, or you may be creating queries that give results other than what you intend.