Close

JPQL LEFT OUTER JOIN ON Condition

[Updated: Aug 28, 2018, Created: Aug 27, 2018]

Starting JPA 2.1, ON condition is also supported which is typically used with LEFT OUTER JOIN. For example

"SELECT e FROM Employee e LEFT OUTER JOIN e.tasks t ON t.supervisor = 'Denise'"

Example

Entities

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  @ManyToMany(cascade = CascadeType.ALL)
  private List<Task> tasks;
    .............
}
@Entity
public class Task {
  @Id
  @GeneratedValue
  private long id;
  private String description;
  private String supervisor;
    .............
}

Queries involving ON condition

First let's not use the 'ON' condition, to understand the difference:

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          executeQuery();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Task task1 = new Task("Coding", "Denise");
      Task task2 = new Task("Refactoring", "Rose");
      Task task3 = new Task("Designing", "Denise");
      Task task4 = new Task("Documentation", "Mike");

      Employee employee1 = Employee.create("Diana", task1, task3);
      Employee employee2 = Employee.create("Mike", task2, task4);
      Employee employee3 = Employee.create("Tim", task3, task4);
      Employee employee4 = Employee.create("Jack");

      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.getTransaction().commit();
      em.close();
      System.out.println("-- Employee persisted --");
      System.out.println(employee1);
      System.out.println(employee2);
      System.out.println(employee3);
      System.out.println(employee4);
  }

  private static void executeQuery() {
      System.out.println("-- executing query --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT DISTINCT e.name, t.description, t.supervisor FROM Employee e LEFT OUTER JOIN e.tasks t");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }
}
-- Employee persisted --
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=8, name='Jack', tasks=[]}
-- executing query --
[Diana, Designing, Denise]
[Mike, Documentation, Mike]
[Diana, Coding, Denise]
[Tim, Documentation, Mike]
[Mike, Refactoring, Rose]
[Jack, null, null]
[Tim, Designing, Denise]

As seen above, the LEFT OUTER JOIN returned all results from the left side table (Employee) even they don't have 'tasks'.

Now Let's use an 'ON' condition on the same query:

public class ExampleMain2 {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          executeQuery();
      } finally {
          entityManagerFactory.close();
      }
  }
    .............
  private static void executeQuery() {
      System.out.println("-- executing query --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT DISTINCT e.name, t.description, t.supervisor FROM Employee e "
                      + "LEFT OUTER JOIN e.tasks t ON t.supervisor = 'Denise'");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }
}
-- Employee persisted --
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=8, name='Jack', tasks=[]}
-- executing query --
[Diana, Designing, Denise]
[Tim, Designing, Denise]
[Diana, Coding, Denise]
[Mike, null, null]
[Jack, null, null]

This time, we got the results only with tasks whose supervisor is 'Denise', but how this is different than the results when the condition is applied via 'WHERE' clause? (see next).

Difference between ON condition and WHERE condition

WHERE condition is applied after tables have been joined.

ON condition is applied on the right side table before joining them.

Let's replace 'ON' with 'WHERE' in our last example:

public class ExampleMain3 {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          executeQuery();
      } finally {
          entityManagerFactory.close();
      }
  }
    .............
  private static void executeQuery() {
      System.out.println("-- executing query --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT DISTINCT e.name, t.description, t.supervisor FROM Employee e "
                      + "LEFT OUTER JOIN e.tasks t WHERE t.supervisor = 'Denise'");
      List<Object[]> resultList = query.getResultList();
      resultList.forEach(r -> System.out.println(Arrays.toString(r)));
      em.close();
  }
}
-- Employee persisted --
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=8, name='Jack', tasks=[]}
-- executing query --
[Diana, Designing, Denise]
[Tim, Designing, Denise]
[Diana, Coding, Denise]

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.3.5.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • JDK 1.8
  • Maven 3.5.4

JPQL LEFT OUTER JOIN ON Condition Select All Download
  • jpql-left-join-on-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain2.java
          • resources
            • META-INF

    See Also