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

Self joins

Correlation names, introduced in the previous section, Restricting a join, are also necessary when joining a single table to itself. In such a situation, your statement contains two separate instances, or copies, of the same table. You can only identify a particular instance by means of a correlation name.

As an example, you might wish to create a list that gives the name of each employee's manager. You can only accomplish this task by joining the employee table to itself.

SELECT e.emp_fname, e.emp_lname,
      m.emp_fname, m.emp_lname
FROM employee AS e JOIN employee AS m
   ON e.manager_id = m.emp_id
ORDER BY e.emp_lname, e.emp_fname

Because this statement includes two copies of the employee table, you must use correlation names to tell them apart. The above command assigns the correlation names e and m to these two copies, respectively. The join condition is that the manager_id value for an employee in instance e of the employee table is equal to the employee_id in instance m of the table.

emp_fname	enp_lname	emp_fname	enp_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