MODE-869 added support for JCR-SQL2 subqueries, but requires that they be non-correlated. It would be extremely helpful if that wasn't the case. Take the following as an example:

SELECT artifact1.* FROM [sramp:baseArtifactType] AS artifact1 
WHERE artifact1.[sramp:artifactType] = 'Part' AND 'element' 
NOT IN (
  SELECT relationship2.[sramp:relationshipType] 
  FROM [sramp:relationship] AS relationship2 
  WHERE ISCHILDNODE(relationship2,artifact1)
)

Since I can't correlate the subquery's artifact1 with the parent query's selector, I'm not sure how else to accomplish something like that (other than in-memory).