ASA SQL User's Guide
  Joins: Retrieving Data from Several Tables
    Specialized joins

Duplicate correlation names in joins (star joins)

The reason for using duplicate table names is to create a star join. In a star join, one table or view is joined to several others.

To create a star join, you use the same table name, view name, or correlation name more than once in the FROM clause. This is an extension to the ANSI/ISO SQL standard. The ability to use duplicate names does not add any additional functionality, but it makes it much easier to formulate certain queries.

The duplicate names must be in different joins for the syntax to make sense. When a table name or view name is used twice in the same join, the second instance is ignored. For example, FROM A,A and FROM A CROSS JOIN A are both interpreted as FROM A.

The following example, in which A, B and C are tables, is valid in Adaptive Server Anywhere. In this example, the same instance of table A is joined both to B and C. Note that a comma is required to separate the joins in a star join. The use of a comma in star joins is specific to the syntax of star joins.

SELECT *
FROM A LEFT OUTER JOIN B ON A.x = B.x,
     A LEFT OUTER JOIN C ON A.y = C.y

The next example is equivalent.

SELECT *
FROM A LEFT OUTER JOIN B ON A.x = B.x,
     C RIGHT OUTER JOIN A ON A.y = C.y

Both of these are equivalent to the following standard ANSI/ISO syntax. (The parentheses are optional.)

SELECT *
FROM (A LEFT OUTER JOIN B ON A.x = B.x)
LEFT OUTER JOIN C ON A.y = C.y

In the next example, table A is joined to three tables: B, C and D.

SELECT *
FROM A JOIN B ON A.x = B.x,
     A JOIN C ON A.y = C.y,
     A JOIN D ON A.w = D.w

This is equivalent to the following standard ANSI/ISO syntax. (The parentheses are optional.)

SELECT *
FROM ((A JOIN B ON A.x = B.x)
JOIN C ON A.y = C.y)
JOIN D ON A.w = D.w

With complex joins, it can help to draw a diagram. The previous example can be described by the following diagram, which illustrates that tables B, C and D are joined via table A.

Example 1 

Create a list of the names of the customers who have placed orders with Rollin Overbey. Notice that one of the tables in the FROM clause, employee, does not contribute any columns to the results. Nor do any of the columns that are joined—such as customer.id or employee.id—appear in the results. Nonetheless, this join is possible only using the employee table in the FROM clause.

SELECT customer.fname, customer.lname,
    sales_order.order_date
FROM    sales_order KEY JOIN customer,
      sales_order KEY JOIN employee
WHERE employee.emp_fname = 'Rollin'
  AND employee.emp_lname = 'Overbey'
ORDER BY sales_order.order_date

Following is the equivalent statement in standard ANSI/ISO syntax:

SELECT customer.fname, customer.lname,
  sales_order.order_date
FROM sales_order JOIN customer
  ON sales_order.cust_id = customer.id
JOIN employee
  ON sales_order.sales_rep = employee.emp_id
WHERE employee.emp_fname = 'Rollin'
  AND employee.emp_lname = 'Overbey'
ORDER BY sales_order.order_date

Example 2 

This example answers the question: How much of each product has each customer ordered, and who is the manager of the salesperson who took the order?

To answer the question, start by listing the information you need to retrieve. In this case, it is product, quantity, customer name, and manager name. Next, list the tables that hold this information. They are product, sales_order_items, customer, and employee. When you look at the structure of the sample database (see Sample database schema), you will notice that these tables are all related through the sales_order table. You can create a star join on the sales_order table to retrieve the information from the other tables.

In addition, you need to create a self-join in order to get the name of the manager, because the employee table contains ID numbers for managers and the names of all employees, but not a column listing only manager names. For more information, see Self-joins.

The following statement creates a star join around the sales_order table. The joins are all outer joins so that the result set will include all customers. Some customers have not placed orders, so the other values for these customers are NULL. The columns in the result set are customer, product, quantity ordered, and the name of the manager of the salesperson.

SELECT customer.fname, product.name,
  SUM(sales_order_items.quantity), m.emp_fname
FROM   sales_order
         KEY RIGHT OUTER JOIN customer,
       sales_order
         KEY LEFT OUTER JOIN sales_order_items
         KEY LEFT OUTER JOIN product,
      sales_order
         KEY LEFT OUTER JOIN employee AS e
         LEFT OUTER JOIN employee AS m
         ON (e.manager_id = m.emp_id)
WHERE customer.state = 'CA'
GROUP BY customer.fname, product.name, m.emp_fname
ORDER BY SUM(sales_order_items.quantity) DESC, customer.fname

Following is a diagram of the tables in this star join. The arrows indicate the directionality (left or right) of the outer joins. As you can see, the complete list of customers is maintained throughout all the joins.

The following standard ANSI/ISO syntax is equivalent to the star join in Example 2.

SELECT customer.fname, product.name,
  SUM(sales_order_items.quantity), m.emp_fname
FROM sales_order LEFT OUTER JOIN sales_order_items
    ON sales_order.id = sales_order_items.id
  LEFT OUTER JOIN product
    ON sales_order_items.prod_id = product.id
  LEFT OUTER JOIN employee as e
    ON sales_order.sales_rep = e.emp_id
  LEFT OUTER JOIN employee as m
    ON e.manager_id = m.emp_id
  RIGHT OUTER JOIN customer
    ON sales_order.cust_id = customer.id
WHERE customer.state = 'CA'
GROUP BY customer.fname, product.name, m.emp_fname
ORDER BY SUM(sales_order_items.quantity) DESC, customer.fname