One of the major benefits of FLWORs is that they can easily join data from multiple sources. For example, suppose you want to join information from your product catalog (catalog.xml) and your order (order.xml). You want a list of all the items in the order, along with their number, name, and quantity. Example 6-13 shows a FLWOR that performs this join.
Example 6-13. Two-way join in a predicate
Query for $item in doc("order.xml")//item, $product in doc("catalog.xml")//product[number = $item/@num] return <item num="{$item/@num}" name="{$product/name}" quan="{$item/@quantity}"/> Results <item num="557" name="Fleece Pullover" quan="1"/> <item num="563" name="Floppy Sun Hat" quan="1"/> <item num="443" name="Deluxe Travel Bag" quan="2"/> <item num="784" name="Cotton Dress Shirt" quan="1"/> <item num="784" name="Cotton Dress Shirt" quan="1"/> <item num="557" name="Fleece Pullover" quan="1"/>
The first part of the for clause selects each item from the order, and the second part selects the matching product element from the catalog.xml document, using a predicate to identify the one whose number matches the item's num attribute. Another way to accomplish the same thing is by using a where clause instead of a predicate, as shown in Example 6-14. This query yields the same results.
Example 6-14. Two-way join in a where clause
for $item in doc("order.xml")//item,
$product in doc("catalog.xml")//product
where $item/@num = $product/number
return <item num="{$item/@num}"
name="{$product/name}"
quan="{$item/@quantity}"/>Whether to use a predicate or a where clause is a matter of personal preference. When many conditions apply, a where clause can be more readable. However, for simple conditions, a predicate may be preferable because it is less verbose. In some implementations, predicates perform faster than where clauses.
Joins can be extended to allow more than two sources to be joined together. For example, suppose that, along with catalog.xml and order.xml, you also want to join the prices.xml document, which contains current pricing information for each product.
The query shown in Example 6-15 joins the prices.xml document with the others to provide pricing information in the results. It uses two expressions in the where clause to implement the two joins.
Example 6-15. Three-way join in a where clause
Query for $item in doc("order.xml")//item, $product in doc("catalog.xml")//product, $price in doc("prices.xml")//prices/priceList/prod where $item/@num = $product/number and $product/number = $price/@num return <item num="{$item/@num}" name="{$product/name}" price="{$price/price}"/> Results <item num="557" name="Fleece Pullover" price="29.99"/> <item num="563" name="Floppy Sun Hat" price="69.99"/> <item num="443" name="Deluxe Travel Bag" price="39.99"/> <item num="557" name="Fleece Pullover" price="29.99"/>
The previous join examples in this section are known as inner joins; the results do not include items without matching products or products without matching items. Suppose you want to create a list of products and join it with the price information. Even if there is no price, you still want to include the product in the list. This is known in relational databases as an outer join.
The query in Example 6-16 performs this join. It uses two FLWORs, one embedded in the return clause of the other. The outer FLWOR returns the list of products, regardless of the availability of price information. The inner FLWOR selects the price, if it is available.
Example 6-16. Outer join
Query for $product in doc("catalog.xml")//product return <product number="{$product/number}">{ attribute price {for $price in doc("prices.xml")//prices/priceList/prod where $product/number = $price/@num return $price/price} }</product> Results <product number="557" price="29.99"/> <product number="563" price="69.99"/> <product number="443" price="39.99"/> <product number="784" price=""/>
Product 784 doesn't have a corresponding price in the prices.xml document, so the price attribute has an empty value for that product.
The where clauses in the join examples use the = operator to determine whether two values are equal. Keep in mind that XQuery considers type when determining whether two values are equal. If schemas are not used with these documents, both values are untyped, and the join shown in Example 6-16 compares the values as strings. Unless they are cast to numeric types, the join does not consider different representations of the same number equal, for example 0557 and 557.
On the other hand, if number in catalog.xml is declared as an xs:integer, and the num attribute in prices.xml is declared as an xs:string, the join will not work. One value would have to be explicitly cast to the other's type, as in:
where $product/number = xs:integer($price/@num)