When using the method contains on a collection (or containsKey , containsValue on a map) this will either add an EXISTS subquery (if there is a NOT or OR present in the query) or will add an INNER JOIN across to the element table. Let's take an example

SELECT FROM org.datanucleus.samples.A
WHERE (elements.contains(b1) && b1.name == 'Jones')
VARIABLES org.datanucleus.samples.B b1

Note that we add the contains first that binds the variable "b1" to the element table, and then add the condition on the variable. The order is important here. If we instead had put the condition on the variable first we would have had to do a CROSS JOIN to the variable table and then try to repair the situation and change it to INNER JOIN if possible. In this case the generated SQL will be like

SELECT `A0`.`ID`
FROM `A` `A0`
INNER JOIN `B` `B0` ON `A0`.ID = `B`.ELEMENT
WHERE `B0`.NAME = 'Jones'

In all situations we aim for DataNucleus JDOQL implementation to work out the right way of linking a variable into the query, whether this is via a join (INNER, LEFT OUTER), or via a subquery. As you can imagine this can be complicated to work out the optimum for all situations so with that in mine we allow (for a limited number of situations) the option of specifying the join type. This is achieved by setting the query extension datanucleus.query.jdoql.{varName}.type to the required type. For 1-1 relations this would be either "INNERJOIN" or "LEFTOUTERJOIN", and for 1-N relations this would be either "INNERJOIN" or "SUBQUERY".

Please, if you find a situation where the optimum join type is not chosen then report it in JIRA for project "NUCRDBMS" as priority "Minor" so it can be registered for future work