Support subqueries in place of static operands (in criteria) in JCR-SQL2 queries

JCR-SQL2 does support 4 kinds of joins: ISSAMENODE, ISCHILD, ISDESCENDANT, and equijoins (where the property on nodes from one selector join is equal to a property on nodes from another selector). However, because there are no pseudo-properties for the node paths, it is not possible to have a join where the paths of the nodes from one selector are equal to property on nodes from the other selector. In SQL-92 (and above), this is often done with subqueries:

SELECT * FROM table WHERE PATH(table) IN ( SELECT pathProperty FROM otherTable WHERE otherProperty LIKE 'truck" )

This query will grab the values of the 'pathProperty' and use them within the IN clause. Other uses are of course possible, such as for comparison criteria using any of the comparison operators (e.g., =, <, <=, LIKE, etc.).

It is possible to effectively do the same kind of thing with multiple queries, but besides requiring multiple queries, it requires more logic on the client to build a second query using the values from the first query.