Get rid of redundant joins produced by subqueries in JPA criteria

Question

I simply need to execute the following MySQL query using JPA criteria (fetching a list of states (from state_table) based on a country name given (in country)).

SELECT state_id, 
       state_name, 
       country_id
FROM   state_table 
WHERE  country_id IN(SELECT country_id 
                     FROM   country 
                     WHERE  country_name = ?)

I have written the following JPA criteria query.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot.get(Country_.countryId));

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));
criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.country).get(Country_.countryId)).value(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();

This criteria query unnecessarily produces a redundant join in the generated SQL query as follows.

SELECT t1.state_id, 
       t1.state_name, 
       t1.country_id 
FROM   projectdb.country t0, 
       projectdb.state_table t1 
WHERE  (t0.country_id IN (SELECT t2.country_id 
                           FROM   projectdb.country t2 
                           WHERE  (t2.country_name = ? )) 
         AND (t0.country_id = t1.country_id ))

As can be noticed, there is a redundant join AND (t0.country_id = t1.country_id )).

This is caused by root.get(StateTable_.countryId).get(Country_.countryId) which implies an inner join in the following line in the criteria query given above . This should not happen unnecessarily in this case.

criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.countryId).get(Country_.countryId)).value(subquery));

How to remove this redundant join?

I'm using JPA 2.0 provided by EclipseLink 2.3.2.

_{Instead of using a subquery in this case, it can better be handled by using a join between these two tables but writing such subqueries without redundant joins in them should be possible, since it is a quite basic thing.}

EDIT 1:

An equivalent EXISTS() query works fine as follows.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
criteriaQuery.select(root);

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot.get(Country_.countryId));

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(root.get(StateTable_.country), subRoot), criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));

criteriaQuery.where(criteriaBuilder.exists(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();

Resulting in producing the following correct SQL query that will in turn be delegated to MySQL.

SELECT t0.state_id, 
       t0.state_name, 
       t0.country_id 
FROM   projectdb.state_table t0 
WHERE  EXISTS (SELECT t1.country_id 
               FROM   projectdb.country t1 
               WHERE  ((t0.country_id = t1.country_id) 
                        AND (t1.country_name = ?)))

Found no way to generate an equivalent IN() query.

EDIT 2:

The following JPQL,

SELECT s 
FROM   StateTable s 
WHERE  s.country IN(SELECT c 
                      FROM   Country c 
                      WHERE  c.countryname = :countryName)

produces the correct IN() subquery.

SELECT t0.state_id, 
       t0.state_name, 
       t0.country_id 
FROM   projectdb.state_table t0 
WHERE  t0.country_id IN (SELECT t1.country_id 
                         FROM   projectdb.country t1 
                         WHERE  (t1.country_name = ?))

From this, I could envision the following criteria query.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<StateTable>criteriaQuery=criteriaBuilder.createQuery(StateTable.class);
Root<StateTable> root = criteriaQuery.from(entityManager.getMetamodel().entity(StateTable.class));
criteriaQuery.select(root);

Subquery<Country> subquery = criteriaQuery.subquery(Country.class);
Root<Country> subRoot = subquery.from(Country.class);
subquery.select(subRoot);

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(subRoot.get(Country_.countryName), parameterExpression));

criteriaQuery.where(criteriaBuilder.in(root.get(StateTable_.country)).value(subquery));
List<StateTable> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "India").getResultList();

But it fails with the following exception at run time.

Exception [EclipseLink-6048] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.QueryException
Exception Description: Illegal use of getField() [projectdb.country.country_id] in expression.
Query: ReadAllQuery(referenceClass=StateTable )
    at org.eclipse.persistence.exceptions.QueryException.illegalUseOfGetField(QueryException.java:563)
    at org.eclipse.persistence.expressions.Expression.getField(Expression.java:1739)
    at org.eclipse.persistence.internal.expressions.FunctionExpression.normalize(FunctionExpression.java:476)
    at org.eclipse.persistence.internal.expressions.SQLSelectStatement.normalize(SQLSelectStatement.java:1402)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.buildNormalSelectStatement(ExpressionQueryMechanism.java:549)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.prepareSelectAllRows(ExpressionQueryMechanism.java:1708)
    at org.eclipse.persistence.queries.ReadAllQuery.prepareSelectAllRows(ReadAllQuery.java:785)
    at org.eclipse.persistence.queries.ReadAllQuery.prepare(ReadAllQuery.java:716)
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:661)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:888)
    at org.eclipse.persistence.queries.DatabaseQuery.checkPrepare(DatabaseQuery.java:613)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:867)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1114)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:402)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1202)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2894)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1797)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1779)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1744)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:258)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:468)
    at client.beans.ProductDetailsBean.test(ProductDetailsBean.java:139)
    at client.beans.ProductDetailsBean.getProductList(ProductDetailsBean.java:119)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1081)
    at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1153)
    at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:4695)
    at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:630)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
    at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:46)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
    at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:582)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doCall(SystemInterceptorProxy.java:163)
    at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:140)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:883)
    at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:822)
    at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:369)
    at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:4667)
    at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4655)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:212)
    at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:88)
    at $Proxy482.getProductList(Unknown Source)
    at client.bean.ProductDetailsManagedBean.load(ProductDetailsManagedBean.java:59)
    at org.primefaces.component.datagrid.DataGrid.loadLazyData(DataGrid.java:144)
    at org.primefaces.component.datagrid.DataGridRenderer.encodeMarkup(DataGridRenderer.java:54)
    at org.primefaces.component.datagrid.DataGridRenderer.encodeEnd(DataGridRenderer.java:47)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:74)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:57)
    at org.primefaces.component.panel.PanelRenderer.encodeContent(PanelRenderer.java:204)
    at org.primefaces.component.panel.PanelRenderer.encodeMarkup(PanelRenderer.java:121)
    at org.primefaces.component.panel.PanelRenderer.encodeEnd(PanelRenderer.java:58)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1864)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:176)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:889)
    at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:70)
    at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:57)
    at org.primefaces.component.layout.LayoutUnitRenderer.encodeEnd(LayoutUnitRenderer.java:51)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:919)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1864)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1860)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:461)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:133)
    at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
    at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:120)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:219)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:344)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:70)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:256)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:316)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
    at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188)
    at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191)
    at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168)
    at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189)
    at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136)
    at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114)
    at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
    at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838)
    at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55)
    at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564)
    at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544)
    at java.lang.Thread.run(Thread.java:722)

I cannot see any reason behind this exception.

EDIT 3:

The same criteria query in the latest revision - the one which fails with the exception, works fine with no modification at all on JPA provided by Hibernate (4.2.7 final) with the exact same table relationship in a different project.

It produces the following correct SQL query.

SELECT
    statetable0_.state_id as state_id1_24_,
    statetable0_.country_id as country_3_24_,
    statetable0_.state_name as state_na2_24_ 
FROM
    social_networking.state_table statetable0_ 
WHERE
    statetable0_.country_id IN (
        SELECT
            country1_.country_id 
        FROM
            social_networking.country country1_ 
        WHERE
            country1_.country_name=?
    )

Therefore, there should something unreliable be happening under the hood in EclipseLink.

Should I safely assume that this is an oversight in EclipseLink in the criteria API?

EDIT 4:

~~The use of EXISTS() as demonstrated above also produces a redundant join, if the query statement is reversed like,~~

SELECT * 
FROM   country c 
WHERE  EXISTS (SELECT s.country_id 
               FROM   state_table s 
               WHERE  s.country_id = c.country_id 
                      AND s.state_name = 'desired_state_name')

JPA provided by Hibernate (I'm currently using 4.2.7 final) generates query statements as we usually expect with no such redundant joins.

This is a very basic query and it is quite hard to believe that this is an oversight in EclipseLink. It seems to me that I must be doing something wrong, must be missing something obvious, very basic. Please clarify.

I have recently upgraded EclipseLink to its current, latest release 2.5.1 having JPA 2.1.

Long story short : Referencing a nested property in an entity like root.get("property1").get("propery2InAnotherRelatedEntity") always causes a superfluous join to be generated which, in general should not happen.

_{Please forgive me, if I have made so many revisions but I had to... :)}

dened · Answer 1 · 2014-04-16T15:58:19.153

Redundant joins in your examples are produced by incorrect JPA criteria query construction and not by presence of subqueries in them. So the title of the question really has not much sense.

To get rid of redundant joins you just need to construct criteria properly. And indeed the criteria query in your EDIT 2 is the correct translation of your original SQL query at the beginning of the question to JPA criteria language, and should not produce any redundant joins. Your experimentation using Hibernate confirms it. And the QueryException which you got is definitely a bug of EclipseLink. I suggest your to report it to developers of EclipseLink: https://wiki.eclipse.org/EclipseLink/Bugs.

But in fact, the subquery is not needed here at all, and joins are usually not evil if used correctly, so I suggest to replace the SQL query with this perfectly equivalent, but more concise one:

SELECT state_id,
       state_name,
       s.country_id
FROM   state_table s, country c
WHERE  s.country_id = c.country_id AND country_name = ?

Its translation to criteria query is also much more simple and clear (and surely will not confuse EclipseLink :)):

CriteriaBuilder criteriaBuilder =
    entityManager.getCriteriaBuilder();

CriteriaQuery<StateTable> criteriaQuery =
    criteriaBuilder.createQuery(StateTable.class);

Root<StateTable> root = criteriaQuery.from(StateTable.class);
ParameterExpression<String> parameterExpression =
    criteriaBuilder.parameter(String.class);

criteriaQuery.where(criteriaBuilder.equal(
    root.get(StateTable_.country).get(Country_.countryName),
    parameterExpression));

List<StateTable> list = entityManager.createQuery(criteriaQuery)
    .setParameter(parameterExpression, "India").getResultList();

And performance of this in MySQL won't be worse than that of the original one.

I was more interested in knowing why EclipseLink produces superfluous, ugly joins while using subqueries in the way as show in the question. It must not happen. It may not be an expected behaviour. — Tiny, Apr 14 '14 at 15:28
The criteria in EDIT 2 of your question is a correct and should not produce any joins. Most prabably, the QueryException you got is a bug in EclipseLink. Regarding EDIT 4, can you show criteria/JPQL you used? — dened, Apr 14 '14 at 16:24
The issue has been [created](https://bugs.eclipse.org/bugs/show_bug.cgi?id=433126). — Tiny, Apr 21 '14 at 05:18

score 2 · Answer 2 · answered Apr 16 '14 at 18:57

2

Does JPA has native methods?
I know hibernate can do complex query by native sql, so that you can do optimization to the sql.

answered Apr 16 '14 at 18:57

Eric

22,183
20
145
196

Yes like `entityManager.createNativeQuery("SQL query")` including some overloaded versions of `createNativeQuery()`. – Tiny Apr 16 '14 at 19:04
@Tiny in that case, the normal way I use dao framework is: write basic utility with things like CriteriaQuery, it would provide CRUD/count/list/query methods automatically for dao level. But for complex query, you use native sql, because there usually don't have much such query. If your application have many complex query, then you might should consider mybatis/ibatis or Spring JdbcTemplate. – Eric Apr 16 '14 at 19:06

score 2 · Answer 3 · answered Apr 17 '14 at 11:54

Could it be something simple like this:

    query = em.createNativeQuery("SELECT state_id, state_name, country_id 
FROM state_table,country
WHERE  state_table.country_id=country.country_id and country.country_name = ?1)").setParameter(1, country_name);
list = (List<Object>) query.getResultList();

where country_name is the parameter you are searching for and list is the list of objects returned by the native query?

Tiny · Answer 4 · 2014-04-16T16:17:29.200

1

This is just a partial / incomplete answer (I could have appended it to the question but there are already so many revisions).

Regarding EDIT 4 in the question, the following criteria query produces a correct SQL query (regarding EXISTS()).

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Country> criteriaQuery = criteriaBuilder.createQuery(Country.class);
Metamodel metamodel = entityManager.getMetamodel();
Root<Country> root = criteriaQuery.from(metamodel.entity(Country.class));
criteriaQuery.select(root);

Subquery<Long> subquery = criteriaQuery.subquery(Long.class);
Root<StateTable> subRoot = subquery.from(metamodel.entity(StateTable.class));
subquery.select(subRoot.get(StateTable_.stateId));

ParameterExpression<String>parameterExpression=criteriaBuilder.parameter(String.class);
subquery.where(criteriaBuilder.equal(root, subRoot.get(StateTable_.countryId)), criteriaBuilder.equal(subRoot.get(StateTable_.stateName), parameterExpression));
criteriaQuery.where(criteriaBuilder.exists(subquery));
List<Country> list = entityManager.createQuery(criteriaQuery).setParameter(parameterExpression, "desired_state_name").getResultList();

This produces the following query.

SELECT t0.country_id, 
       t0.country_code, 
       t0.country_name, 
       t0.zone_id 
FROM   projectdb.country t0 
WHERE  EXISTS (SELECT t1.state_id 
               FROM   projectdb.state_table t1 
               WHERE  ((t0.country_id = t1.country_id)
                        AND (t1.state_name = ?)))

_{I'm not going to accept my own answer anymore.}

edited Apr 16 '14 at 16:17

answered Apr 14 '14 at 18:55

Tiny

27,221
105
339
599

The subquery in the example with `EXIST()` is a dependant (correlated) subquery and potentially very dangerous with respect to performance. It would be a smart move if a JPA engine really rewrites such subquery as a join. – dened Apr 16 '14 at 16:07
The example with `IN()` is incorrect as you link together unrelated fields `country_id` and `state_id`... – dened Apr 16 '14 at 16:14
Thank you for taking time. I still don't have enough experience with RDBMS but joins might not always be an alternative of `EXISTS()`. In this particular case, I find joins unnecessary because I need a result set from one table only based on the conditional check in another related table. An equivalent join query would produce duplicate rows in the result set, if child rows are repeated. So, we need to filter the result using `DISTINCT`. – Tiny Apr 16 '14 at 16:17
I deleted the `IN()` query as it was indeed incorrect. I didn't pay much attention to it. – Tiny Apr 16 '14 at 16:18
One thing I can see Hibernate, as long as I have done so far, always produces queries (including subqueries, (`IN()` or `EXISTS()`)) that we usually expect. – Tiny Apr 16 '14 at 16:28
In this example the join query would produce duplicate rows only if multiple states in a country have same name. But generally I argee, `EXISTS()` in some cases may be more practical than join. And thankfully RDBMS usually optimizes usage of dependant subqueries in `EXISTS()`. – dened Apr 16 '14 at 16:48

Get rid of redundant joins produced by subqueries in JPA criteria

4 Answers4

Linked