JPQL RIGHT JOIN Issue

Question

I'm trying to select the same fields between two tables using RIGHT JOIN... I got the error message 'Path expected for join! [select P.cin,P2.cin from com.kachafa.domain.Participant as P RIGHT JOIN ParticipantTwo as P2 ON P.cin =: P2.cin]'

name 1st data table : Participant // name 2nd data table : ParticipantTwo code as below :

public List<Participant> compareListParticipant(){
    List<Participant> result = null;
    
    try {
        String jpql = "select P.cin,P2.cin from Participant as P RIGHT JOIN ParticipantTwo as P2 ON P.cin =: P2.cin" ;
        Query query = entityManager.createQuery(jpql);
        result = query.getResultList();
        
    } catch (Exception e) {
        e.printStackTrace();
    }
    return result;
}

score 0 · Answer 1 · answered Feb 09 '23 at 14:37

Although JPQL is similar to SQL, there are many ways of thinking that are not interchangeable and it seems you fell in one of them.

JPQL JOIN sintax requires you to define a path from the previous Entity:

join ::= join_spec join_association_path_expression AS identification_variable

As such, you can only use JPQL JOINs if the given entity has some relationship with the target joined entity - JPA implementation will be responsible to build the necessary equality on SQL JOINs. When there is no relationship, you should use comma separated list of joined entities.

Let's imagine your Participant entity has a relation with the ParticipantTwo entity such as Participant.parent. In that case, you may use JPQL to fetch results using SELECT p1.id, p2.id FROM Participant AS p1 JOIN p.parent AS p2 WHERE p1.name = p2.name'.

If there is no relationship, you must use the comma strategy for comparison:

SELECT p1.id, p2.id FROM Participant AS p1, ParticipantTwo AS p2 WHERE p1.name = p2.name

That should do the trick for you.

Your suggested query is an inner join, not a right outer join. Also, HQL *does* allow `right join`s between query roots, even if JPA doesn't. — Gavin King, Feb 09 '23 at 18:40
True, but I thought, based on his query, that it was that what he wanted. My mistake. — Paulo Araújo, Feb 14 '23 at 13:31

score 0 · Accepted Answer · answered Feb 09 '23 at 15:30

I didnt reach to solve the problem with the JPQL join, however I i did the below method with two lists, it works for me :

 private  List<String> getDifference(List<String> listA, List<String> listB) {
        Set<String> set = new HashSet<>(listA);
        List<String> result = new ArrayList<>();

        for (String element : listB) {
          if (!set.contains(element)) {
            result.add(element);
          }
        }

        return result;
      }

score 0 · Answer 3 · answered Feb 09 '23 at 18:39

So Paulo is correct to say that JPA doesn't require support for right join, however, Hibernate HQL does allow this syntax. I just tested the following query in Hibernate 6, and it worked correctly:

select e1.id, e2.id from EntityOfBasics e1 right join EntityOfBasics e2 on e1.id=e2.id

This produced the following SQL:

select
    e1_0.id,
    e2_0.id 
from
    EntityOfBasics e1_0 
right join
    EntityOfBasics e2_0 
        on e1_0.id=e2_0.id

I do notice that your query uses a nonexistent =: operator in the on clause. So possibly that is the problem.

JPQL RIGHT JOIN Issue

3 Answers3