To understand how a simple inner join is computed, consider the following query. It answers the question: which product sizes have been ordered in the same quantity as the quantity in stock?
SELECT DISTINCT Name, Size, SalesOrderItems.Quantity FROM Products JOIN SalesOrderItems ON Products.ID = SalesOrderItems.ProductID AND Products.Quantity = SalesOrderItems.Quantity; |
name | Size | Quantity |
---|---|---|
Baseball Cap | One size fits all | 12 |
Visor | One size fits all | 36 |
You can interpret the query as follows. Note that this is a conceptual explanation of the processing of this query, used to illustrate the semantics of a query involving a join. It does not represent how SQL Anywhere actually computes the result set.
Create a cross product of the Products table and SalesOrderItems table. A cross product contains every combination of rows from the two tables.
Exclude all rows where the product IDs are not identical (because of the join condition Products.ID = SalesOrderItems.ProductID
).
Exclude all rows where the quantity is not identical (because of the join condition Products.Quantity = SalesOrderItems.Quantity
).
Create a result table with three columns: Products.Name, Products.Size, and SalesOrderItems.Quantity.
Exclude all duplicate rows (because of the DISTINCT keyword).
For a description of how outer joins are computed, see Outer joins.
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |