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,

[cc lang="sql"]select u.NAME, b.ITEMNO, b.BID from USERS u, BIDS b where u.USERID = b.USERID[/cc]

In XQuery this is expressed as follows,

[cc lang="xquery"]for $user in collection("USERS")/USERS for $bid in collection("BIDS")/BIDS where $user/USERID = $bid/USERID return { $user/NAME, $bid/ITEMNO, $bid/BID }[/cc]

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,

[cc lang="xquery"]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 { $user/NAME, $bid/ITEMNO, $bid/BID, $item/DESCRIPTION }[/cc]

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,

[cc lang="sql"]select u.NAME, b.ITEMNO, b.BID from USERS u left outer join BIDS b on u.USERID = b.USERID[/cc]

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.

[cc lang="xquery"]for $user in collection("USERS")/USERS return { $user/NAME, for $bid in collection("BIDS")/BIDS where $user/USERID = $bid/USERID return { $bid/ITEMNO, $bid/BID } }[/cc]

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.

[cc lang="xquery"]...

Jack Sprat

1003 20

1007 200

Rip Van Winkle

...[/cc]

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,

[cc lang="xquery"]for $user in collection("USERS")/USERS for $bid in collection("BIDS")/BIDS where $user/USERID = $bid/USERID return { $user/NAME, $bid/ITEMNO, $bid/BID }[/cc]

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,

[cc lang="xquery"]for $user in collection("USERS")/USERS where fn:empty(collection("BIDS")/BIDS[USERID = $user/USERID]) return { $user/NAME }[/cc]

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,

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

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

[cc lang="xquery"]where fn:empty(for $bid in collection("BIDS")/BIDS where $bid/USERID = $user/USERID return $bid)[/cc]

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,

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

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

[cc lang="xquery"] Jack Sprat 1003 20

Jack Sprat 1007 200

Rip Van Winkle [/cc]

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,

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

What about subselects?

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

[cc lang="xquery"]select (select u.NAME from USERS u where u.USERID = b.USERID), b.ITEMNO, b.BID from BIDS b[/cc]

Similar in XQuery you can write,

[cc lang="xquery"]for $bid in collection("BIDS")/BIDS return { for $user in collection("USERS")/USERS where $user/USERID = $bid/USERID return $user/NAME, $bid/ITEMNO, $bid/BID }[/cc]

Or more concise,

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

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

[cc lang="sql"]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)

[/cc]Rather than repeating the expression twice, use a let-clause in XQuery,

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

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.