XMLTable and outer join methods

While assisting with a question on the OTN forums (Using XML clob in loop)

I had to pause briefly to remember how to do outer joins when passing optional (aka child may not exist) information from one XMLTable to another XMLTable.

The syntax I first used was the proprietary Oracle method of using (+). So the FROM clause looked something like

XMLTABLE(...COLUMNS ... xmlfrag XMLTYPE PATH ... ) x, 
XMLTABLE(... PASSING x.xmlfrag ...) (+) y

So now the query returns data from "x" even in there was no data in "y". Then I got to wondering what the ANSI standard for writing that XMLTable join would be. Turns out it is just a simple

XMLTABLE(...COLUMNS ... xmlfrag XMLTYPE PATH ... ) x
LEFT OUTER JOIN
XMLTABLE(... PASSING x.xmlfrag ...) y
ON 1=1

The ON clause exists because some type of join condition is required for a LEFT/RIGHT OUTER JOIN. Normally a 1=1 would cause a Cartesian join between the two tables but in this case it doesn't because the join conditions are already defined as the second XMLTable is joined to the first XMLTable via the PASSING clause.

Pretty simple.

* Caution *
Be wary when using the ANSI JOIN syntax on older versions of Oracle. Here is the reason why Problem with XMLTABLE and LEFT OUTER JOIN . I know I've seen talk/blogs regarding bugs with Oracle's implementation of the ANSI JOIN syntax in certain situations. As with any SQL statement, you always need to verify your output.