Optional ‘Paths’ in JPQL Queries

There exists a phenomenon in JPQL/SQL that isn’t quite intuitive in the first place, but gets clearer after you have understood how it works. A normal JOIN in a JPQL query is evaluated as an INNER JOIN. That means, that a row will not be selected, if the right hand side of the JOIN is NULL. This may sometimes lead to unexpected results…

In the next listing the employes with one of the given superiors and department are returned, for example:

SELECT emp
FROM
	Employee AS emp,
	IN(emp.superior) sup
WHERE
	(
		sup = IN(:superiors)
	AND
		emp.department = :dep
	)

Imagine, you would like to return the same employees and additionally the superiors themselves. You might be tempted to write the following query:

SELECT emp
FROM
	Employee AS emp,
	IN(emp.superior) sup
WHERE
	(
		(
			emp = IN(:employees)
		OR
			sup = IN(:employees)
		)
	AND
		emp.department = :dep
	)

But, the IN([alias1].[step]) [alias2] statement in the FROM clause represents an inner JOIN, so that values that are NULL in the right set ([alias2]) lead to omitting the row even though the boolean condition sup = IN(:employees) is in an OR clause. So every employee, that does not have a superior will be omitted (em.superior == null). But this is not intended of course. So, using a LEFT JOIN tackles this problem:

SELECT emp
FROM
	Employee AS emp
	LEFT JOIN emp.superior sup
WHERE
	(
		(
			emp = IN(:employees)
		OR
			sup = IN(:employees)
		)
	AND
		emp.department = :dep
	)

The same problem exists for the semantically equivalent variants [INNER] JOIN (as a replacement for IN()) and implicit INNER JOINs like this:

SELECT emp
FROM
	Employee AS emp,
	Employee AS sup
WHERE
	(
		sup = IN(:employees)
	AND
		(
			emp = IN(:employees)
		OR
			emp.superior = sup
		)
	AND
		emp.department = :dep
	)