Introduction to XQuery (Part 3 of 4) : Page 4

Using Joins in XQuery
In SQL, one of the most powerful ways to process data stored in Relational Data Base Management Systems (RDBMS) is to join data from multiple tables. A join operation usually combines data from multiple tables. With FLWR expressions you can use XQuery to perform similar functions on multiple documents.

In the last example, two for clauses were nested in the return clause of the outer FLWR expression. In this case, the tuples of variable bindings are drawn from the Cartesian product of the sequences returned by the expressions in all the for clauses. The ordering of the sequences from which they were formed, from left to right, governs the ordering of the tuples . For example, the preceding query returned only those items from items.xml whose itemno matched an itemno in PO.xml. Conventionally, this type of relationship is called an inner join.

An outer join is a join that preserves information from one or more of the participating documents, including elements that have no matching element in the other documents. Consider the following query, which lists all customers as well as the purchase order id for those customers who have placed orders.

   # see XQuery36.ixq in samples.zip
   for $u in document("data/customers.xml")//customer
   return
      <customer id={$u/custno}>
         <name>{$u//firstname/text()}   {$u//lastname/text()}</name>
         { 
           for $p in document("data/PO.xml")//po
           where $u/custno = $p//custno
           return 
           <po>{$p/@id}</po>
         }
      </customer>

The query returns the following result:

   <customer id="9000">
       <name> Joe Anderson </name>
       <po id="0001"/>
   </customer>
   <customer id="1001">
       <name> Andy Shaperd </name>
       <po id="0002"/>
   </customer>
   <customer id="1003">
       <name> Amanda Johnson </name>
   </customer>
   <customer id="2005">
       <name> Bill Murphy </name>
   </customer>

You can see that the query result lists all the customers, regardless of whether or not they have placed a po. Conventionally, a join that selects all the items from the left side of the join is called a left outer join.

At this point, you should be able to see that XQuery provides very sophiscated mechanism to process and transform data in XML documents through FLWR expressions, built-in functions, user defined functions, and conditional operations.