The Hibernate WITH clause, the HQL equivalent of SQL's ON

One of the powerful features of SQL is that you can JOIN tables ON certain conditions.  This can be really useful as you try to refine your searches.  Hibernate(HQL) offers something similar, but before giving an example here is something very important to understand if you don't want implementation specific code in your app:

HQL also defines a WITH clause to qualify the join conditions. Again, this is specific to HQL; JPQL does not define this feature.

 So with that here is the example the Hibernate docs give:


One gotcha to lookout for is using the WITH clause with related objects. This example creates an error (although I can't remember what it is):

But you can fix it by doing this:

Here are the Hibernate docs related to the WITH clause