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 SQL 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.
You can use duplicate table names only if the extended_join_syntax option is On (the default).
For more information, see extended_join_syntax option [database].
Create a list of the names of the customers who placed orders with Rollin Overbey. Notice that one of the tables in the FROM clause, Employees, does not contribute any columns to the results. Nor do any of the columns that are joined—such as Customers.ID or Employees.EmployeeID—appear in the results. Nonetheless, this join is possible only using the Employees table in the FROM clause.
SELECT Customers.GivenName, Customers.Surname, SalesOrders.OrderDate FROM SalesOrders KEY JOIN Customers, SalesOrders KEY JOIN Employees WHERE Employees.GivenName = 'Rollin' AND Employees.Surname = 'Overbey' ORDER BY SalesOrders.OrderDate; |
GivenName | Surname | OrderDate |
---|---|---|
Tommie | Wooten | 2000-01-03 |
Michael | Agliori | 2000-01-08 |
Salton | Pepper | 2000-01-17 |
Tommie | Wooten | 2000-01-23 |
... | ... | ... |
Following is the equivalent statement in standard ANSI/ISO syntax:
SELECT Customers.GivenName, Customers.Surname, SalesOrders.OrderDate FROM SalesOrders JOIN Customers ON SalesOrders.CustomerID = Customers.ID JOIN Employees ON SalesOrders.SalesRepresentative = Employees.EmployeeID WHERE Employees.GivenName = 'Rollin' AND Employees.Surname = 'Overbey' ORDER BY SalesOrders.OrderDate; |
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 Products, SalesOrderItems, Customers, and Employees. When you look at the structure of the SQL Anywhere sample database (see Sample database schema), you will notice that these tables are all related through the SalesOrders table. You can create a star join on the SalesOrders table to retrieve the information from the other tables.
In addition, you need to create a self-join to get the name of the manager, because the Employees 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 SalesOrders 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 Customers, Products, Quantity ordered, and the name of the manager of the salesperson.
SELECT Customers.GivenName, Products.Name, SUM(SalesOrderItems.Quantity), m.GivenName FROM SalesOrders KEY RIGHT OUTER JOIN Customers, SalesOrders KEY LEFT OUTER JOIN SalesOrderItems KEY LEFT OUTER JOIN Products, SalesOrders KEY LEFT OUTER JOIN Employees AS e LEFT OUTER JOIN Employees AS m ON (e.ManagerID = m.EmployeeID) WHERE Customers.State = 'CA' GROUP BY Customers.GivenName, Products.Name, m.GivenName ORDER BY SUM(SalesOrderItems.Quantity) DESC, Customers.GivenName; |
GivenName | Name | SUM(SalesOrderItems.Quantity) | GivenName |
---|---|---|---|
Sheng | Baseball Cap | 240 | Moira |
Laura | Tee Shirt | 192 | Moira |
Moe | Tee Shirt | 192 | Moira |
Leilani | Sweatshirt | 132 | Moira |
... | ... | ... | ... |
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 Customers.GivenName, Products.Name, SUM(SalesOrderItems.Quantity), m.GivenName FROM SalesOrders LEFT OUTER JOIN SalesOrderItems ON SalesOrders.ID = SalesOrderItems.ID LEFT OUTER JOIN Products ON SalesOrderItems.ProductID = Products.ID LEFT OUTER JOIN Employees as e ON SalesOrders.SalesRepresentative = e.EmployeeID LEFT OUTER JOIN Employees as m ON e.ManagerID = m.EmployeeID RIGHT OUTER JOIN Customers ON SalesOrders.CustomerID = Customers.ID WHERE Customers.State = 'CA' GROUP BY Customers.GivenName, Products.Name, m.GivenName ORDER BY SUM(SalesOrderItems.Quantity) DESC, Customers.GivenName; |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |