o7planning

JPA Join types and syntax in JPQL

  1. Entities
  2. Inner JOIN
  3. Left Outer JOIN
  4. Right Outer JOIN
  5. JOIN in WHERE clause
  6. Multiple JOIN clauses
  7. Fetch JOIN
As you know, SQL supports 7 different JOIN types. In this article we will learn what JOIN types are supported by JPA and look at examples in JPQL (Java Persistence Query Language).
  • Get Started with JPA JPQL

1. Entities

Let's look at the sample data model we'll use in the examples.
Employee entity - simulates an employee.
Employee.java
package org.o7planning.sample_bank_db.entity;

// Imports

@Entity
@Table(name = "Employee")
public class Employee implements Serializable { 

	@Id
	@GeneratedValue
	@Column(name = "Emp_Id", nullable = false)
	private Long empId;

	@Column(name = "Start_Date", nullable = false)
	private LocalDate startDate;

	@Column(name = "End_Date", nullable = true)
	private LocalDate endDate;

	@Column(name = "First_Name", length = 32, nullable = false)
	private String firstName;

	@Column(name = "Last_Name", length = 32, nullable = false)
	private String lastName;

	@Column(name = "Title", length = 32, nullable = false)
	private String title;

	@ManyToOne
	@JoinColumn(name = "Dept_Id", nullable = true, //
			foreignKey = @ForeignKey(name = "Employee_Department_Fk"))
	private Department department;

	@ManyToOne
	@JoinColumn(name = "Superior_Emp_Id", nullable = true, //
			foreignKey = @ForeignKey(name = "Employee_Employee_Fk"))
	private Employee superiorEmployee;

	// Other properties and Getters, Setters.

}
Department entity - simulates a department.
Department.java
package org.o7planning.sample_bank_db.entity;

// Imports

@Entity
@Table(name = "Department")
public class Department implements Serializable { 

	@Id
	@GeneratedValue
	@Column(name = "Dept_Id", nullable = false)
	private Long deptId;

	@Column(name = "Name", length = 256, nullable = false)
	private String name;

	@OneToMany(fetch = FetchType.LAZY, mappedBy = "department")
	private Set<Employee> employees = new HashSet<>(0);

	// Other properties and getters, setters
}
The data model used in this article is a sample database, simulating the data of a bank. If you are interested, you can download the project below, it is written on Spring Boot + JPA and supports all types of databases. When you run the project, tables and data are automatically created.
  • JPA Sample Bank Database

2. Inner JOIN

First, let's look at an INNER JOIN query in SQL:
SELECT d.* FROM Employee e
  INNER JOIN Department d on e.dept_id = d.dept_id
  Where e.first_name = 'Susan';
  
-- Same As:

SELECT d.* FROM Employee e
  JOIN Department d on e.dept_id = d.dept_id
  Where e.first_name = 'Susan';
  • SQL Inner JOIN
Explicit INNER JOIN:
In explicit INNER JOIN syntax, the keyword "INNER JOIN" or "JOIN" is used. Both of these keywords can be used with the same meaning.
The relationship between the two entities Employee and Department has been determined through the "department" property of Employee, so the "ON" keyword is not necessary.
InnerJoinExample1.java
package org.o7planning.jpa_jpql.java_14321;

import java.util.List;

import org.o7planning.sample_bank_db.entity.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;

@Component
public class InnerJoinExample1 {

	@Autowired
	private EntityManager entityManager;

	public void test() {
		String jpql = "Select d from Employee e " //
				+ " join e.department d " //
				+ " where e.firstName = 'Susan'";
		//
		TypedQuery<Department> query = entityManager.createQuery(jpql, Department.class);
	
		List<Department> departments = query.getResultList();
		this.showResults(departments);
	}
	
	private void showResults(List<Department> departments) {
		for (Department dept : departments) {
			System.out.println("Dept: " + dept.getName());
		}
	}
}
Implicit INNER JOIN:
INNER JOIN syntax can be used implicitly (No need to use keywords "JOIN" and "INNER JOIN").
InnerJoinExample2.java
public void test() {
	String jpql = "Select e.department from Employee e " //
			+ " where e.firstName = 'Susan'";
	//
	TypedQuery<Department> query = entityManager.createQuery(jpql, Department.class);

	List<Department> departments = query.getResultList();
	this.showResults(departments);
}
INNER JOIN explicitly with a collection
InnerJoinExample3.java
package org.o7planning.jpa_jpql.java_14321;

import java.util.List;

import org.o7planning.sample_bank_db.entity.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;

@Component
public class InnerJoinExample3 {

	@Autowired
	private EntityManager entityManager;

	public void test() {
		String jpql = "Select e from Department d " //
				+ " JOIN d.employees e " //
				+ " where d.name = 'Operations' " //
				+ " and e.firstName like 'S%' ";
		//
		TypedQuery<Employee> query = entityManager.createQuery(jpql, Employee.class);

		List<Employee> employees = query.getResultList();
		this.showResults(employees);
	}

	private void showResults(List<Employee> employees) {
		for (Employee emp : employees) {
			System.out.println("Emp: " + emp.getFirstName() + " " + emp.getLastName());
		}
	}
}
Output:
Emp: Susan Hawthorne
Emp: Sarah Parker
Emp: Samantha Jameson
Implicit INNER JOIN with a collection
public void test() {
	String jpql = "Select d.employees from Department d " //
			+ " where d.name = 'Operations' ";
	//
	TypedQuery<Employee> query = entityManager.createQuery(jpql, Employee.class);

	List<Employee> employees = query.getResultList();
	this.showResults(employees);
}

private void showResults(List<Employee> employees) {
	for (Employee emp : employees) {
		System.out.println("Emp: " + emp.getFirstName() + " " + emp.getLastName());
	}
}

3. Left Outer JOIN

LEFT OUTER JOIN syntax uses the keyword "LEFT JOIN" or "LEFT OUTER JOIN", both of which have the same meaning.
SELECT d.*, e.emp_id, e.dept_id
	FROM department d
	LEFT JOIN Employee e on d.dept_Id = e.dept_Id;
	
-- Same As ---

SELECT d.*, e.emp_id, e.dept_id
	FROM department d
	LEFT OUTER JOIN Employee e on d.dept_Id = e.dept_Id;
	
  • SQL Outer JOIN
You can see the difference in results between the two queries "LEFT OUTER JOIN" and "INNER JOIN" in the example below. "LEFT OUTER JOIN" returns results including all records of the left table, even if the right table has no records that satisfy the condition of the "ON" clause.
LEFT OUTER JOIN
INNER JOIN
SELECT d.*, e.emp_id, e.dept_id
 FROM Department d
 LEFT JOIN Employee e 
    on d.dept_Id = e.dept_Id
 order by d.dept_id;
SELECT d.*, e.emp_id, e.dept_id
 FROM Department d
 JOIN Employee e 
     on d.dept_Id = e.dept_Id
 order by d.dept_id;
Find departments with no employees or with at least one employee named "Susan".
OuterJoinExample1.java
public void test() {
	// Find departments with no employees or employees named "Susan".
	String jpql = "Select d from Department d " //
			+ " LEFT JOIN d.employees e " //
			+ " where e is null or e.firstName = 'Susan'";
	//
	TypedQuery<Department> query = entityManager.createQuery(jpql, Department.class);

	List<Department> departments = query.getResultList();
	this.showResults(departments);
}

private void showResults(List<Department> departments) {
	for (Department dept : departments) {
		System.out.println("Dept: " + dept.getName());
	}
}
Output:
Dept: Operations
Dept: Administration
Dept: IT

4. Right Outer JOIN

"RIGHT OUTER JOIN" returns results including all records of the right table, even if the left table has no records that satisfy the condition of the "ON" clause.
RIGHT OUTER JOIN
INNER JOIN
SELECT e.emp_id, e.dept_id, d.*
 FROM Employee e 
 RIGHT JOIN Department d 
    on e.dept_Id = d.dept_Id
 order by d.dept_Id
SELECT e.emp_id, e.dept_id, d.*
 FROM Employee e 
 JOIN Department d
     on e.dept_Id = d.dept_Id
 order by d.dept_id
Find departments with no employees or with at least one employee named "Susan".
RightOuterJoinExample1.java
public void test() {
	// Find departments with no employees or employees named "Susan".
	String jpql = "Select DISTINCT d from Employee e " //
			+ " RIGHT JOIN e.department d " //
			+ " where e is null or e.firstName = 'Susan'";
	//
	TypedQuery<Department> query = entityManager.createQuery(jpql, Department.class);

	List<Department> departments = query.getResultList();
	this.showResults(departments);
}

private void showResults(List<Department> departments) {
	for (Department dept : departments) {
		System.out.println("Dept: " + dept.getName());
	}
}
Output:
Dept: Operations
Dept: Administration
Dept: IT

5. JOIN in WHERE clause

SQL:
SELECT DISTINCT d.* 
 FROM Department d, Employee e
Where d.dept_id = e.dept_id  
and e.first_name = 'Susan'
For JPQL, we can also put two or more Entities in the FROM clause and specify join conditions in the WHERE clause.
JoinInWhereExample1.java
public void test() { 
	String jpql = "Select DISTINCT d from Department d, Employee e " // 
			+ " where e.department = d"
			+ " and e.firstName = 'Susan'";
	//
	TypedQuery<Department> query = entityManager.createQuery(jpql, Department.class);

	List<Department> departments = query.getResultList();
	this.showResults(departments);
}

private void showResults(List<Department> departments) {
	for (Department dept : departments) {
		System.out.println("Dept: " + dept.getName());
	}
}

6. Multiple JOIN clauses

Combine multiple Entities in one JPQL query:
For example: Find the departments of the highest level employees (Employees who are not under the management of any other employee).
MultiJoinExample1.java
public void test() {
	String jpql = "Select DISTINCT d from Employee e " //
			+ " left join e.superiorEmployee se " //
			+ " join e.department d " //
			+ " where se is null ";
	//
	TypedQuery<Department> query = entityManager.createQuery(jpql, Department.class);

	List<Department> departments = query.getResultList();
	this.showResults(departments);
}

private void showResults(List<Department> departments) {
	for (Department dept : departments) {
		System.out.println("Dept: " + dept.getName());
	}
}

7. Fetch JOIN

"FETCH JOIN" is a special concept in JPQL that SQL does not have. There is quite a lot to say about this topic so I explain it in a separate article. If you are interested, you can see the article below:
  • JPA JPQL Fetch JOIN
Type
Keyword
INNER JOIN FETCH
  • INNER JOIN FETCH
  • JOIN FETCH
LEFT OUTER JOIN FETCH
  • LEFT OUTER JOIN FETCH
  • LEFT JOIN FETCH
RIGHT OUTER JOIN FETCH
  • RIGHT OUTER JOIN FETCH
  • RIGHT JOIN FETCH