18. JPQL Where Clause — Subqueries

Definition of Subqueries in JPQL

-> A subquery in JPQL is a nested query that is embedded within another JPQL query. It is enclosed within parentheses and can be used in the **where, `having**, orfrom `clauses of the outer query. Subqueries enable more complex logic by allowing a query to depend on the result of another query.

-> Briefly, subqueries are queries within queries (queries means → outer query or in otherwords our main query)

Remark: Most subqueries in JPQL are used with the **where** clause.

When Should We Use Subqueries? (Generalized Rule to Keep in Mind)

-> Subqueries in JPQL are used when we need to dynamically generate a set of values or a set of entity instances within the where clause of the main query. These generated values or instances that are generated by our subquery -> will act as a condition to decide whether the fetched instances (selected like select e) by the main query satisfy the condition for selection.

In other words;

-> Subqueries are most often used within the where clause of a main query (outer query), and their results (namely, a set of values or instances) are used to determine whether the entity instances fetched by the main query satisfy the filtering criteria.

Common Use Cases for Subqueries:

1. Comparing attributes with aggregated values.
2. Filtering entity instances based on related entities or their attributes.
3. Checking for the existence of related instances.
4. Matching attributes against dynamically generated set of values.

— — — — — — — — — — — — — — — — — — -

#Task: Fetch (retrieve) the employee with the highest basic salary in the relevant database.

→ Use a subquery to compute the maximum salary and compare each employee’s salary with that maximum salary value and return or retrieve the certain-specific Employee instance with the highest salary.

Implementation and Step-by-Step Examination of the Method

public class QueryRepository {

@Inject
EntityManager entityManager;

 public Employee getEmployeeWithHighestSalary() {

  TypedQuery<Employee> queryThatHoldsEmployeeWithHighestSalary = 
     this.entityManager.createQuery("select e from Employee e 
    where e.basicSalary = (select max(emp.basicSalary) from Employee emp)",
    Employee.class);

      Employee employeeWithHighestSalary = 
      queryThatHoldsEmployeeWithHighestSalary.getSingleResult();

     return employeeWithHighestSalary; 

        // Alternative and Shorter Implementation: 
        // return this.entityManager.createQuery("select e from Employee e 
        // where e.basicSalary = 
        // (select max(emp.basicSalary) from Employee emp) ", 
        // Employee.class).getSingleResult();
    }
}

• So, after the **where** clause in our main query (”select e from Employee e where e.basicSalary=”) we issue another query which will be our subquery.

-> Namely, the query we are going to issue here, is what we called a subquery.

• We should be aware that, we are using our subquery in the conditional part of the main query !

-> Therefore, the result from the subquery is → what is going to be used to determine the value of this particular condition.

• As a result, the result of the subquery (in other words; the salary of the person with the highest salary among the employees, in other words, the highest salary value) is → what is going to be used as condition of our main query.
• We are saying that, fetch the employee whose basicSalary is = “what”? → Our subquery will find that → “what” .

(select max(emp.basicSalary) from Employee emp) 
// subquery after the where clause of our main (or outer) query

• This subquery will return the maximum basicSalary value as a condition to → where e.basicSalary = i.e, as a condition to our main query .
• As a result ; we are using a subquery to fetch the highest basicSalary attribute value in the Employee entity (highest basicSalary column value in the Employee table) and using that as the conditional value, to say that any employee that has that particular basicSalary, return that to us.

— — — — — — — — — — — — — — — — — — — — — — — — -

An In-Depth Examination of the Implementation Step-By-Step

Step 1: Purpose of the Code

-> The purpose of this code is to retrieve the certain-specific employee (employee instance) with the highest basic salary in the database. It uses a subquery to compute and find the maximum salary value that employees have and use that maximum salary value -> as the condition of the outer query (main query) to fetch the employee with the highest salary.

Step 2: Breakdown of the JPQL Query

1. Main Query (Outer Query):

"select e from Employee e"

-> selects all employee entity instances from the relevant db.

2. where clause

"where e.basicSalary = (...)"

-> The **where** clause filters the results of the outer query to include only employees whose basicSalary matches the result of the subquery.

3. Subquery:

"(select max(emp.basicSalary) from Employee emp)"

• This subquery calculates and returns the maximum basicSalary across all employees (emp) in the Employee table.
• The subquery returns a single value (the maximum salary), which the outer query uses as a condition for the WHERE clause.

Execution Flow

1. The subquery executes first:

• It computes and returns the maximum salary by iterating through the Employee table and finding the highest basicSalary.

2. The result of the subquery (a single value) is used in the **where clause of the outer query.**

3. The outer query then selects -> the certain-specific employee whose basicSalary matches the value returned by the subquery.

4. The result of the outer query is returned as a single Employee entity instance.

Execution Flow Summary

• Subquery Execution: Computes and returns the maximum salary by iterating through the Employee table and finding the highest basicSalary.
• Outer (Main) Query Execution: The result of the subquery (a single value) is used in the **where** clause of the outer query.
• Employee Selection: The outer query selects the employee whose basicSalary matches the maximum salary returned by the subquery.

— — — — — — — — — — — — — — — — — — — — — — — — -

When Do We Use JPQL Subqueries?

1. Comparing Specific Attributes (attribute of entity instances that we want to fetch) with Aggregated Values

• Purpose : When we need to filter entity instances based on an aggregated (calculated) value (or metric) (e.g., average, max, count) derived from other entities.
• How it works: The subquery calculates an aggregated value (like avg(), sum(), or max()), and the main or outer query compares individual entity attributes against this value.

Use Case: When filtering entity instances based on an aggregated value (e.g., avg(), sum(), or max()).

"select e from Employee e where e.basicSalary > 
      (select avg(emp.basicSalary) from Employee emp)"

Execution Flow

1. Subquery Execution: Computes the average salary.
2. Outer Query Execution: Filters employees earning above the average.

-> Result of the query will be: This query selects employees whose basic salary is above the average salary of all employees.

Sample Data as JPQL Query Output:

Employee Instances:

[
  {
    "id": 1,
    "name": "John",
    "basicSalary": 50000
  },
  {
    "id": 2,
    "name": "Emma",
    "basicSalary": 60000
  },
  {
    "id": 3,
    "name": "Mike",
    "basicSalary": 70000
  },
  {
    "id": 4,
    "name": "Alice",
    "basicSalary": 80000
  },
  {
    "id": 5,
    "name": "David",
    "basicSalary": 40000
  }
]

• Average Basic Salary = (50000 + 60000 + 70000 + 80000 + 40000) / 5 = 60000
• Employees whose basicSalary is greater than 60000:

-> Mike (70000)

-> Alice (80000)

JPQL Query Result:

[
  {
    "id": 3,
    "name": "Mike",
    "basicSalary": 70000
  },
  {
    "id": 4,
    "name": "Alice",
    "basicSalary": 80000
  }
]

— — — — — — — — — — — — — —

2. Filtering Based on Related (Associated) Entities or Their Attributes

• Purpose: To include entity instances in the main query only if they are related to a specific subset of other entities.
• How it works: The subquery fetches a set of related values (like department IDs) that meet a condition, and the main query checks if the entity instances belongs to this set.

Use Case: Filtering entities based on relationships.

Example: Employees working in departments with more than 10 employees.

"select e from Employee e where e.department.id in 
   (select d.id from Department d where size(d.employees) > 10)"

Clarification on size(d.employees) > 10) in JPQL

In JPQL, we can not directly select a collection-valued association (e.g., select d.employees from Department d). JPQL does not support returning collections as query results. Instead, it requires explicit joins to access collection elements.

However, in the JPQL query:

"select e from Employee e where e.department.id in 
    (select d.id from Department d where size(d.employees) > 10)"

-> We are not selecting the employees collection explicitly. Instead, we are using the size() function, which is a valid JPQL function that operates on collection-valued relationships without requiring a join.

How Does size(d.employees) > 10 Work?

• The size() function returns the number of elements in the employees collection for each department (d).

"(select d.id from Department d where size(d.employees) > 10)"

• The subquery retrieves only the IDs of departments that have more than 10 employees.

"select e from Employee e where e.department.id in (subquery)"

• Then, the outer query selects employees belonging to those departments.

Why Don’t We Need a JOIN Here?

In JPQL, joins are required when:

• We need to fetch entity instance(s) or attributes from a related (target) collection.
• We want to filter data using attributes inside a collection.

-> However, in the case of size(d.employees), we are not fetching collection elements—we are only checking the count.

So Important Key Awareness As a Result -> We do not need an explicit **join in `size(d.employees) > 10** because JPQL’ssize()` function operates directly on the collection. However, if we wanted to access an attribute inside d.employees, we would need a **join**

Key Points of our Relevant JPQL Query:

• Set Operator Used: in operator checks if the employee’s department.id (i.e → e.department.id) matches any of the department IDs returned by the subquery.
• Subquery Purpose: (select d.id from Department d where d.employees.size > 10) identifies departments with more than 10 employees.
• Mechanism: The subquery returns -> a set of department IDs, and the outer query -> filters employees belonging to those departments.

Sample Data as JPQL Query Output:

Employee Table:

[
  {
    "id": 1,
    "name": "John",
    "departmentId": 1
  },
  {
    "id": 2,
    "name": "Sarah",
    "departmentId": 2
  },
  {
    "id": 3,
    "name": "Mike",
    "departmentId": 3
  },
  {
    "id": 4,
    "name": "Alice",
    "departmentId": 1
  }
]

Department Table:

[
  {
    "id": 1,
    "name": "HR",
    "numberOfEmployees": 12
  },
  {
    "id": 2,
    "name": "Engineering",
    "numberOfEmployees": 8
  },
  {
    "id": 3,
    "name": "Sales",
    "numberOfEmployees": 15
  }
]

JPQL Query Result:

[
  {
    "id": 1,
    "name": "John",
    "departmentId": 1
  },
  {
    "id": 3,
    "name": "Mike",
    "departmentId": 3
  },
  {
    "id": 4,
    "name": "Alice",
    "departmentId": 1
  }
]

— — — — — — — — — — — — — — — — —

3. Checking for Existence

Purpose of exists keyword: exists is an efficient and declarative keyword for checking the existence of related instances. Often preferred when dealing with relationships.

Use Case: Checking if an entity has related instance(s).

Example: Employees who have received at least one bonus.

Remark: Employee-Bonus entities have -> One-To-Many Relationship

" select e from Employee e where exists 
   (select b from Bonus b where b.employee = e) "

Key Points and Summary:

• Existence Operator Used: exists checks whether the subquery returns any records (instances) for each employee.
• Subquery Purpose: (select b from Bonus b where b.employee = e) checks if a Bonus record exists for each employee.
• Mechanism: For each employee, the subquery checks for a matching bonus. If at least one bonus exists, the employee is included in the result.

Remark about Performance: exists is often more efficient than **inkeyword in large datasets because it stops execution once it finds a match. In other words; `exists**is generally preferred for performance in large datasets because it stops searching as soon as a match is found, whereasIN` evaluates the entire result set.

Sample Data as the JPQL Query Result:

Employee Table:

[
  {
    "id": 1,
    "name": "John"
  },
  {
    "id": 2,
    "name": "Sarah"
  },
  {
    "id": 3,
    "name": "Mike"
  },
  {
    "id": 4,
    "name": "Alice"
  }
]

Bonus Table:

[
  {
    "id": 1,
    "employeeId": 1,
    "amount": 500
  },
  {
    "id": 2,
    "employeeId": 3,
    "amount": 700
  }
]

Execution:

1. Subquery Execution for Each Employee:

• For John: (select b from Bonus b where b.employee = e) → Exists (Bonus ID 1 ✅).
• For Sarah: (select b from Bonus b where b.employee = e) → Does Not Exist ❌.
• For Mike: (select b from Bonus b where b.employee = e) → Exists (Bonus ID 2 ✅).
• For Alice: (select b from Bonus b where b.employee = e) → Does Not Exist ❌.

2. Outer Query Execution:

• Employees with bonuses:
• -> John
• -> Mike

JPQL Query Result:

[
  {
    "id": 1,
    "name": "John"
  },
  {
    "id": 3,
    "name": "Mike"
  }
]

— — — — — — — — — — — — — — — — — — —

4. Comparing an Attribute to a Set of Values

• Purpose: To filter entity instances in the main query by checking if their attribute matches one of the values in a dynamically generated set.
• How it works: The subquery retrieves a set of values, and the main query uses these values in a condition (e.g., in clause).

Use Case: Filtering by -> dynamically generated sets.

Example: Fetch products supplied by suppliers with a 5-star rating:

Remark: Supplier-Product entities have One-To-Many relationship

"select p from Product p where p.supplier.id in 
    (select s.id from Supplier s where s.rating = 5)"

Sample Data as the JPQL Query Result:

Product Table:

[
  {
    "id": 1,
    "name": "Laptop",
    "supplierId": 1
  },
  {
    "id": 2,
    "name": "Smartphone",
    "supplierId": 2
  },
  {
    "id": 3,
    "name": "Headphones",
    "supplierId": 3
  },
  {
    "id": 4,
    "name": "Keyboard",
    "supplierId": 1
  }
]

Supplier Table:

[
  {
    "id": 1,
    "name": "TechCorp",
    "rating": 5
  },
  {
    "id": 2,
    "name": "MobileMasters",
    "rating": 4
  },
  {
    "id": 3,
    "name": "AudioKing",
    "rating": 5
  }
]

Execution:

1. Subquery Execution: “select s.id from Supplier s where s.rating = 5"

Suppliers with a rating of 5:

• Supplier ID 1 (TechCorp) ✅
• Supplier ID 3 (AudioKing) ✅

2. Outer (Main) Query Execution: “select p from Product p where p.supplier.id in (1, 3)”

Products whose supplier.id is in {1, 3}:

• Laptop (Supplier 1 ✅)
• Headphones (Supplier 3 ✅)
• Keyboard (Supplier 1 ✅)

JPQL Query Result:

[
  {
    "id": 1,
    "name": "Laptop",
    "supplierId": 1
  },
  {
    "id": 3,
    "name": "Headphones",
    "supplierId": 3
  },
  {
    "id": 4,
    "name": "Keyboard",
    "supplierId": 1
  }
]

— — — — — — — — — — — — -

Summary of Key Concepts in General

1. Aggregate Functions (e.g., avg(), max(), min())

• Used to compute summary statistics (e.g., average salary).
• Example: Filter employees earning above the average salary.

-> **avg(emp.basicSalary)**

2. Set Operator (in)

• Used to check if a value exists within a set of results.
• Example: Select employees in departments with more than 10 employees.

-> **in (select d.id from Department d where size(d.employees) > 10)**

-> **in (select s.id from Supplier s where s.rating = 5)**

3. Existence Operator (exists)

• Used to check whether a related subquery returns any rows.
• Example: Find employees who have received bonuses.

-> **exists (select b from Bonus b where b.employee = e)**