User's Guide
   PART 1. Working with Databases
     CHAPTER 6. Joins: Retrieving Data from Several Tables       

Self-joins and correlation names

Joins can compare values within the same column, or two different columns of a single table. These joins are called self-joins. For example, you can create a list all the employees and the name of each person's manager by joining the employee table to itself.

In such a join, you cannot distinguish the columns by the conventional means because the join will contain two copies of every column.

For example, suppose you want to create a table of employees that includes the names of their managers. The following query does not answer this question.

SELECT *
FROM employee JOIN employee
   ON employee.manager_id = employee.emp_id

In fact, this statement is semantically equivalent to the much simpler statement below.

SELECT *
FROM employee

When constructing joins, Adaptive Server Anywhere treats all tables or views that have the same name as the same instance of a view or table. The name employee is treated as only one instance of the employee table because the same name appears at both locations in the FROM clause.

Use correlation names to distinguish instances of a table 

To distinguish an individual instance of a table, use a correlation name. A correlation name is an alias for an instance of a table or view. You define a correlation name in the FROM clause. Once defined, you must use the correlation name in place of the table name elsewhere within your statement, including the selection list, wherever you refer to that instance of the table.

The following statement uses the correlation names report and manager to distinguish the two instances of the employee table and so correctly creates the list of employees and their managers.

SELECT report.emp_fname, report.emp_lname,
   manager.emp_fname, manager.emp_lname
FROM employee AS report JOIN employee AS manager
   ON report.manager_id = manager.emp_id
ORDER BY report.emp_lname, report.emp_fname

This statement produces the result shown below. The employee names appear in the two left-hand columns and the names of their managers on the right.

emp_fname	emp_lname	emp_fname	emp_lname
Alex	Ahmed	Scott	Evans
Joseph	Barker	Jose	Martinez
Irene	Barletta	Scott	Evans
Jeannette	Bertrand	Jose	Martinez
Janet	Bigelow	Mary Anne	Shea
Barbara	Blaikie	Scott	Evans
Jane	Braun	Jose	Martinez
Robert	Breault	David	Scott
Matthew	Bucceri	Scott	Evans
Joyce	Butterfield	Scott	Evans

Using correlation names 

Choose short, concise correlation names to make your statements easier to read. In many cases, names only one or two characters in length will suffice.

While you must use correlation names for a self-join to distinguish multiple instances of a table, they can make many other statements more readable too. For example, the statement

SELECT customer.fname, customer.lname,
      sales_order.id, sales_order.order_date
FROM customer KEY JOIN sales_order
WHERE      customer.fname = 'Beth'
   AND      customer.lname = 'Reiser'

becomes more compact if you use the correlation name c for customer and so for sales_order:

SELECT c.fname, c.lname, so.id, so.order_date
FROM customer AS c KEY JOIN sales_order AS so
WHERE      c.fname = 'Beth'
   AND      c.lname = 'Reiser'

For brevity, you can even eliminate the keyword AS. It is redundant because the syntax of the SQL language identifies the correlation names: they are separated from the corresponding table name by only a space, not a comma.

SELECT c.fname, c.lname, so.id, so.order_date
FROM customer c KEY JOIN sales_order so
WHERE      c.fname = 'Beth'
   AND      c.lname = 'Reiser'

     For further details of the rules regarding correlation names and instances of a table within a FROM clause, see Joining more than two tables.