User's Guide

Part II. Tutorials

Chapter 8. Joining Tables

The most natural way to make a join useful is to insist that the sales_rep in the sales_order table be the same as the one in the employee table in every row of the result. Then each row contains information about an order and the sales rep responsible for it.

Example 1

To do this, add a WHERE clause to the previous query to show the list of employees and their course registrations:

     SELECT *
     FROM sales_order, employee
     WHERE sales_order.sales_rep = employee.emp_id

The table name is given as a prefix to identify the columns. Although not strictly required in this case, using the table name prefix clarifies the statement, and is required when two tables have a column with the same name. A table name used in this context is called a qualifier.

The results of this query contain only 648 rows (one for each row in the sales_order table). Of the original 48,600 rows in the join, only 648 of them have the employee number equal in the two tables.

Example 2

The following query is a modified version that fetches only some of the columns and orders the results.

     SELECT employee.emp_lname, sales_order.id, sales_order.order_date
     FROM sales_order, employee
     WHERE sales_order.sales_rep = employee.emp_id
     ORDER BY employee.emp_lname

If there are many tables in a SELECT command, you may need to type several qualifier names. This typing can be reduced by using a correlation name.

Correlation names

Correlation names are created by putting a short form for a table name immediately after the table name, separated by the word AS . The short form is then used as a qualifier instead of the corresponding table name, as follows:

     SELECT E.emp_lname, S.id, S.order_date
     FROM sales_order as S, employee as E
     WHERE S.sales_rep = E.emp_id
     ORDER BY E.emp_lname

Here, two correlation names S and E are created for the sales_order and employee tables.

If you try changing E.emp_id back to employee.emp_id, SQL Anywhere reports an error. If you make a correlation name for a table, you must use the correlation name when qualifying which table a column is from; you cannot use the original table name anymore.