JPA Join types and syntax in JPQL
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.
No ADS
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);
}No ADS
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 JamesonImplicit 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 |
| |
 |  |
No ADS
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: IT4. 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 |
| |
 |  |
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: IT5. 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'No ADS
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
No ADS
"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 |
|
LEFT OUTER JOIN FETCH |
|
RIGHT OUTER JOIN FETCH |
|
No ADS