In a previous post I talked about performing "inner joins" in XQuery. The basic idea is that the inner join is simply a special case of a Cartesian product, or "cross join".  In this post I'm going to briefly look at another common SQL-style join condition that can be duplicated to some degree in XQuery. Specifically I'm going to give an example of a retrieving nodes from a tuple stream that don't match the nodes in a second tuple stream.

In this post we'll duplicate about 50% of the SQL "left outer join" functionality. For those who aren't familiar with SQL left outer joins, they can be thought of as an inner join between two tables unioned together with the rows of the left-hand table that have no corresponding rows in the right-hand table.

For this example I'm going to borrow some slightly modified XML data from the previous post:

DECLARE @xml xml;
-- Create sample XML document
SET @xml = N'<authors>
  <author id = "1">Fabio Claudio Ferracchiati</author>
  <author id = "2">Hugo Kornelis</author>
  <author id = "3">Rob Walters</author>
  <author id = "4">Lara Rubbelke</author>
  <author id = "5">Adam Machanic</author>
  <author id = "6">Michael Coles</author>
  <author id = "7">Robin Dewson</author>
  <author id = "8">Jan D. Narkiewicz</author>
  <author id = "9">Robert Rae</author>
</authors>
<books>
  <book title = "Pro T-SQL 2005 Programmer&apos;s Guide">
    <isbn>159059794X</isbn>
    <author>6</author>
  </book>
  <book title = "Accelerated SQL Server 2008">
    <isbn>1590599691</isbn>
    <author>3</author>
    <author>6</author>
    <author>7</author>
    <author>1</author>
    <author>8</author>
    <author>9</author>
  </book>
  <book title = "Pro SQL Server 2008 XML">
    <isbn>1590599837</isbn>
    <author>6</author>
  </book>
</books>';
-- Perform outer join
SELECT @xml.query('for $author in /authors/author
  where fn:empty($author[@id = /books/book/author])
  return <author> { $author } </author>');

This sample is available as a download file here.

The XML data consists of a list of authors and a list of books. The query uses a FLWOR expression to retrieve all authors who don't have a corresponding book node. Here's how it works:

<author>
  <author id="2">Hugo Kornelis</author>
</author>
<author>
  <author id="4">Lara Rubbelke</author>
</author>
<author>
  <author id="5">Adam Machanic</author>
</author>

As you can see, the three authors with id's of (2, 4, 5) were returned because they have no corresponding book nodes in the XML data. This technique, combined with the previously discussed inner join technique, can be used to simulate SQL style outer joins in XQuery.

One of the more interesting features of XQuery is FLWOR expressions (FLWOR stands for "for, let, where, order by, return", the keywords in the FLWOR expression vocabulary). FLWOR expressions have several capabilities, many of which are well-documented, but some of which get little attention.

One of the lesser-publicized features I found interesting is the "inner join" capability. XQuery has the ability to generate the Cartesian product ("cross join") of two path expressions using a for clause format like this:

for $var1 in path1, $var2 in path2 

Technically speaking, this for clause "binds the tuple stream" of path1 to $var1, binds the tuple stream of path2 to $var2, and generates the Cartesian product of the two variables.  If path1 generated the tuple stream (tom, joe, larry), and path2 generated the tuple stream (johnson, washington), then the result of the cross join would be:

tom johnson
tom washington
joe johnson
joe washington
larry johnson
larry washington

Every possible combination of the two tuple streams is generated. Now I promised you an inner join, didn't I? That's actually pretty simple when you consider the inner join to be a special case of the cross join. For instance, consider the old-style SQL syntax (pre-SQL-92) for inner joins. It looked something like this:

SELECT bookName
FROM book, author
WHERE book.AuthorId = author.Id

This statement performs a cross join between the book and author tables, and then restricts the results with a WHERE clause. This is the essence of the inner join. We can do the same thing with the FLWOR expression in XQuery, as shown below:

DECLARE @xml xml;
-- Create sample XML document

SET @xml = N'<authors>
  <author id = "1">Fabio Claudio Ferracchiati</author>
  <author id = "2">Hugo Kornelis</author>
  <author id = "3">Rob Walters</author>
  <author id = "4">Lara Rubbelke</author>
  <author id = "5">Adam Machanic</author>
  <author id = "6">Michael Coles</author>
  <author id = "7">Robin Dewson</author>
  <author id = "8">Jan D. Narkiewicz</author>
  <author id = "9">Robert Rae</author>
</authors>
<books>
  <book title = "Pro T-SQL 2005 Programmer&apos;s Guide">
    <isbn>159059794X</isbn>
    <author>6</author>
  </book>
  <book title = "Expert SQL Server 2005 Development">
    <isbn>159059729X</isbn>
    <author>5</author>
    <author>4</author>
    <author>2</author>
  </book>
  <book title = "Linq for Visual C# 2005">
    <isbn>1590598261</isbn>
    <author>1</author>
  </book>
  <book title = "Accelerated SQL Server 2008">
    <isbn>1590599691</isbn>
    <author>3</author>
    <author>6</author>
    <author>7</author>
    <author>1</author>
    <author>8</author>
    <author>9</author>
  </book>
  <book title = "Pro SQL Server 2008 XML">
    <isbn>1590599837</isbn>
    <author>6</author>
  </book>
</books>';

-- Perform inner join
SELECT @xml.query('for $author in /authors/author, $book in /books/book
  where $author/@id = $book/author
  and $author eq "Fabio Claudio Ferracchiati"
  return <book> { $book/@title } </book>');

Here's how this FLWOR expression works, step-by-step:

1. The query binds the tuple stream from the /authors/author path to the variable $author.
2. Then it binds the tuple stream /books/book to the $book variable.
3. It generates the cross join of the two tuple streams.
4. The where clause limits the results to the ones where the author's id attribute matches one of the book's author elements.
5. Then the results are restricted again by the second half of the compound predicate: the final results will only include those where the author's name is "Fabio Claudio Ferracchiati".
6. Finally, node construction is used to return the title of the books this author has written as XML nodes.

The result of this XQuery FLWOR expression is shown below:

<book title="Linq for Visual C# 2005" />
<book title="Accelerated SQL Server 2008" />

As you can see FLWOR expressions make inner joins, as a subset of cross joins, possible in XQuery.