XQuery for the SQL programmer – Joining Data

Often you join multiple table in SQL queries. What about XQuery, how can we perform joins in XQuery? This is what this third post in the XQuery for the SQL programmer series is all about.

A first example

Assume you want to get all users with their bids. In SQL one could write,

select u.NAME, b.ITEMNO, b.BID
from USERS u, BIDS b
where u.USERID = b.USERID

In XQuery this is expressed as follows,

for $user in collection("USERS")/USERS
for $bid in collection("BIDS")/BIDS
where $user/USERID = $bid/USERID
return
  <result>{
    $user/NAME,
    $bid/ITEMNO,
    $bid/BID
  }</result>

Conceptually, the for-clauses of a FLWOR expression generate an ordered sequence of tuples of variable bindings. Note the analogy with a cartesian-join in SQL (a cross-join if you want). Next the where-clause filters the sequence of tuples. In our example above this results in the appropriate join condition being applied.
Note XQuery doesn't have the concept of result sets, everything is XML. We create a small XML fragment in the return-clause, combining the user's name with the bid's information.

The example above showed a join between two tables. But just like in SQL, XQuery allows to have more complex joins. Simply add additional for-clauses to your FLWOR expression. The next example is based on the previous, but in addition the bids are joined with items to retrieve the corresponding item description,

for $user in collection("USERS")/USERS
for $bid in collection("BIDS")/BIDS
for $item in collection("ITEMS")/ITEMS
where $user/USERID = $bid/USERID and
      $bid/ITEMNO = $item/ITEMNO
return
  <result>{
    $user/NAME,
    $bid/ITEMNO,
    $bid/BID,
    $item/DESCRIPTION
  }</result>

A word on outer joins

Up to now we've talked about inner join, what about outer joins? Get all users with their corresponding bids,

select u.NAME, b.ITEMNO, b.BID
from USERS u left outer join BIDS b
on u.USERID = b.USERID

In XQuery this can be expressed building hierarchical XML results, for each user we group the bids. Right, forget about tabular only data, in XML you can create hierarchical structures.

for $user in collection("USERS")/USERS
return
  <user>{
    $user/NAME,
    for $bid in collection("BIDS")/BIDS
    where $user/USERID = $bid/USERID
    return
      <bid>{
        $bid/ITEMNO,
        $bid/BID
    }</bid>
  }</user>

For example, Jack Sprat placed two bids, one for item 1003 and another on item 1007. Rip Van Winkle on the other hand doesn't have yet any bids.

...
<user>
  <NAME>Jack Sprat</NAME>
  <bid>
    <ITEMNO>1003</ITEMNO>
    <BID>20</BID>
  </bid>
  <bid>
    <ITEMNO>1007</ITEMNO>
    <BID>200</BID>
  </bid>
</user>
<user>
  <NAME>Rip Van Winkle</NAME>
</user>
...

Suppose now you want to represent the results in a more tabular structure. This requires a bit more work in XQuery...
Remember the first join example in this post,

for $user in collection("USERS")/USERS
for $bid in collection("BIDS")/BIDS
where $user/USERID = $bid/USERID
return
  <result>{
    $user/NAME,
    $bid/ITEMNO,
    $bid/BID
  }</result>

This yields all the user-bid tuples we're looking for, but excludes the users not having placed any bids. Retrieving those users can be achieved as follows,

for $user in collection("USERS")/USERS
where fn:empty(collection("BIDS")/BIDS[USERID = $user/USERID])
return
  <result>{
    $user/NAME
  }</result>

We simply get all users, and only consider those which have no bids through the where-clause. Note that we use a more concise path expression in,

where fn:empty(collection("BIDS")/BIDS[USERID = $user/USERID])

If you prefer to use FLWOR expressions consistently the previous query becomes,

where fn:empty(for $bid in collection("BIDS")/BIDS
               where $bid/USERID = $user/USERID
               return $bid)

Note this is only about syntax difference. Both forms are semantically equivalent, and good XQuery implementations yield the same performance characteristics.

Back to our outer join subject, now we can combine both queries using the comma-operator,

for $user in collection("USERS")/USERS
for $bid in collection("BIDS")/BIDS
where $user/USERID = $bid/USERID
return
  <result>{
    $user/NAME,
    $bid/ITEMNO,
    $bid/BID
  }</result>
,
for $user in collection("USERS")/USERS
where fn:empty(collection("BIDS")/BIDS[USERID = $user/USERID])
return
  <result>{
    $user/NAME
  }</result>

For Jack Sprat and Rip Van Winkle we get results as follows,

<result>
  <NAME>Jack Sprat</NAME>
  <ITEMNO>1003</ITEMNO>
  <BID>20</BID>
</result>
<result>
  <NAME>Jack Sprat</NAME>
  <ITEMNO>1007</ITEMNO>
  <BID>200</BID>
</result>
<result>
  <NAME>Rip Van Winkle</NAME>
</result>

You can fairly easy extend the last query to perform a full outer join. Only a matter of adding a third sub-expression.

If you want to order the result by name and itemno, simply wrap the previous query with an additional FLWOR expression,

for $result in
 (for $user in collection("USERS")/USERS
  for $bid in collection("BIDS")/BIDS
  where $user/USERID = $bid/USERID
  return
    <result>{
      $user/NAME,
      $bid/ITEMNO,
      $bid/BID
    }</result>
  ,
  for $user in collection("USERS")/USERS
  where fn:empty(collection("BIDS")/BIDS[USERID = $user/USERID])
  return
    <result>{
      $user/NAME
    }</result>)
order by
  $result/NAME
return
  $result

What about subselects?

In SQL we often use subselects. For example, get a list of all bids with the name of the bidder,

select (select u.NAME from USERS u where u.USERID = b.USERID),
       b.ITEMNO,
       b.BID
from BIDS b

Similar in XQuery you can write,

for $bid in collection("BIDS")/BIDS
return
  <bid>{
    for $user in collection("USERS")/USERS
    where $user/USERID = $bid/USERID
    return
      $user/NAME,
  $bid/ITEMNO,
  $bid/BID
}</bid>

Or more concise,

for $bid in collection("BIDS")/BIDS
return
  <bid>{
    collection("USERS")/USERS[USERID = $bid/USERID]/NAME,
    $bid/ITEMNO,
    $bid/BID
}</bid>

Suppose we want to order the bids by the bidder's name,

select (select u.NAME from USERS u where u.USERID = b.USERID),
        b.ITEMNO,
        b.BID
from BIDS b
ORDER BY (select u.NAME from USERS u where u.USERID = b.USERID)

Rather than repeating the expression twice, use a let-clause in XQuery,

for $bid in collection("BIDS")/BIDS
let $name := collection("USERS")/USERS[USERID = $bid/USERID]/NAME
order by $name
return
  <bid>{
    $name,
    $bid/ITEMNO,
    $bid/BID
}</bid>

Similar to SQL, where you can use subselects at various locations in a select statement, this also applies to XQuery. FLWOR expressions can be nested. In fact, XQuery is much more open to this than SQL. Where in SQL a subselect can only be used in specific locations, in XQuery every expressions can be combined with any other expression.

In SQL you have a rich set of functions, in our next post we'll compare this with the functions and operators available in SQL.

Tech Tags: XQuery SQL