JPA Outer join

The following two tabs change content below.
Prasad Kharkar is a java enthusiast and always keen to explore and learn java technologies. He is SCJP,OCPWCD, OCEJPAD and aspires to be java architect.

Latest posts by Prasad Kharkar (see all)

Continuing with our JPA joins, this article will deal with JPA outer joins. This article will elaborate how JPA outer joins are different from inner joins.

JPA Outer Join:

The syntax for JPA Outer Join query is as follows.

  • LEFT specifies this is LEFT OUTER JOIN
  • OUTER keyword is optional.
  • [AS] is optional and specifies alias for join.

To understand JPA Outer Join effectively, we will take help of inner join. Consider a scenario in which Teacher and Department entities are related. Database diagram for them is as follows.

Outer join

The data in Teacher table is

idTeacher firstName lastName salary idDepartment
3 Prasad Kharkar 20000  null
4 Sushsant Pangarkar 40000 6
5 Pranil Gilda 20000 8

As you can see there are 3 teacher in database and two are assigned to departments with ids 6 and 8.
If you write the INNER JOIN query as follows and execute it,

You will get Teacher entities which are assigned to some department. The result is as follows.

But what if we want all the teachers irrespective of their belonging to department or not? In this case we need an OUTER join.

JPA Outer join of Teacher to Department across the relationship returns all the teachers and the department to which they are assigned. This is in contrast with INNER join where only the teachers assigned to some department are returned.

Let us write JPA Outer Join query to test it out.

This will output,

Here you can see, a Teacher with name “Prasad” wasn’t included in the results of INNER JOIN query but included in OUTER JOIN query even if it is not assigned to any department.

I hope this article helped understand the concept of JPA Outer joins.

 

References for further study :

Share Button

Prasad Kharkar

Prasad Kharkar is a java enthusiast and always keen to explore and learn java technologies. He is SCJP,OCPWCD, OCEJPAD and aspires to be java architect.

5 thoughts on “JPA Outer join

  • Pingback:Criteria api joins | theJavaGeek

  • May 28, 2015 at 8:30 pm
    Permalink

    Thank you! it helped me!

    Reply
  • August 11, 2015 at 6:37 pm
    Permalink

    This blog is pretty good for hardcore practices but it would be better you put a complete bunch of required entities.

    Reply
  • February 6, 2016 at 12:20 am
    Permalink

    Does the strategy extend to multiple tables, say where A has one or more B and B has one or more C?

    SELECT a from A a LEFT JOIN a.b b
    LEFT JOIN b.c c
    WHERE a……

    I have this find in SQL but no luck for JPA. Any suggestions?

    Thanks,
    Henry

    Reply
  • November 11, 2016 at 6:28 pm
    Permalink

    Hi, what about full outer join? JPQL doesn’t recognize keyword ‘full’

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *