In this example, we will see how to use LEFT OUTER JOIN queries in JPQL.

A LEFT OUTER JOIN (or LEFT JOIN) query selects all records from left table even if there are no matching records in right side table.

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;

  public Task() {
  }
    .............
}

OUTER LEFT JOIN Query

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 FROM Employee e LEFT OUTER JOIN e.tasks t");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      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 --
Employee{id=8, name='Jack', tasks=[]}
Employee{id=7, name='Tim', tasks=[Task{id=3, description='Designing', supervisor='Denise'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=4, name='Mike', tasks=[Task{id=5, description='Refactoring', supervisor='Rose'}, Task{id=6, description='Documentation', supervisor='Mike'}]}
Employee{id=1, name='Diana', tasks=[Task{id=2, description='Coding', supervisor='Denise'}, Task{id=3, description='Designing', supervisor='Denise'}]}

The difference between this example and the last example is that, above query returns employee 'Jack' as well even though he is not assigned any task.

Also above query returns the same result as this simple query will do:

SELECT DISTINCT e from EMPLOYEE e

So what is the advantage of 'LEFT OUTER JOIN'? This join query can be useful where we want to see the matching records but at the same time want to see the record from the first table where matching values in the join condition may be absent. Let's see following example to understand that:

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 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 --
[Tim, Designing]
[Tim, Documentation]
[Jack, null]
[Mike, Refactoring]
[Diana, Coding]
[Mike, Documentation]
[Diana, Designing]

Note that the OUTER keyword is optional so our last query can be written as:

SELECT DISTINCT e.name, t.description FROM Employee e LEFT JOIN e.tasks t

Example Project

Dependencies and Technologies Used:

• h2 1.4.197: H2 Database Engine.
• hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  Implements javax.persistence:javax.persistence-api version 2.1
• JDK 1.8
• Maven 3.3.9