JPA Query optimization by avoiding JOIN to lookup table?...

Question

Imagine a table emp:

CREATE TABLE emp
( id           NUMBER
, name         VARCHAR
, dept_code    VARCHAR
)

and a table dept:

CREATE TABLE dept
( code         VARCHAR
, name         VARCHAR
)

emp.dept_code references dept.code as a ForeignKey.

These tables are mapped to JPA Entities, and the ForeignKey is modeled as an association:

@ManyToOne
@JoinColumn(name = "dept_code")
private Department department;

Given following data:

emp                     dept    
----------------        ------------------
1    John   SALS        SALS     Sales
2    Louis  SALS        SUPT     Support
3    Jack   SUPT 
4    Lucy   SUPT  

I would like to write a JPA query that returns all Emloyees in the Support Department. Assume I know the PrimaryKey of the Support Department (SUPT)

I guess that would be:

SELECT emp
  FROM Employee emp JOIN emp.department dept
 WHERE dept.code = 'SUPT'

Question:

As the Department key SUPT code is available in the emp table, is there a way to rewrite the JPA query by avoiding the JOIN to the Department Entity?

Would this result in a performance improvement? Or is a JPA implementation (like Hibernate) smart enough to avoid the database join to the dept table?

Answer 1

You would usually write the query as

select emp
from employee emp
where emp.department.code = 'SUPT'

and let your provider figure out the best way to come up with the result. In the case of hibernate, yes, it is smart enough to realize it can just look at the join column.

edit : It is worth noting, that you haven't set up lazy loading in your annotations, so it's going to join the table in to create the department entity anyway :)