Performing a LEFT OUTER JOIN using JPQL and JPA

Haven’t made a tutorial-type post in a while so here’s an installment of documenting something that should be already documented. The problem at hand is a very common one: how do you do a LEFT OUTER JOIN on two tables (entities) when you’re using the Java Persistence API.

Let’s go with the sports theme and model basketball shoe sponsorship. An NBA player may or may not have a shoe deal, for example Lebron James has a shoe deal with Nike while Juan Dixon doesn’t. We have two tables and entities to work with: Player and ShoeCompany. Now the task at hand is to retrieve all information about all players. Given that many of them don’t have shoe deals we want to be careful of not ignoring them in our query, that’s where the OUTER JOIN kicks in. In other words, retrieve the set of entities where matching values in the join condition may be absent. Here’s what the entities and tables look like:

@Entity
@Table( name="nba_player" )
public class Player {

    @Id @Column
    private Long id;

    @Column
    private String name;

    @OneToOne
    @JoinColumn( name="shoecompany_id" )
    private ShoeCompany shoeCompany;

    // getters and setters
}

@Entity
@Table( name="shoe_company" )
public class ShoeCompany {

    @Id @Column
    private Long id;

    @Column
    private String name;

    // getters and setters
}

Here’s what the tables look like:

nba_player(id, name, shoecompany_id)
shoe_company(id, name)

Doing this query without an OUTER JOIN is also possible, it’s quite simple actually, we just add an additional WHERE clause where we check for the foreign key being NULL.

SELECT *
FROM nba_player p, shoe_company s
WHERE p.shoecompany_id = s.id or p.shoecompany_id IS NULL

The same SQL query using an OUTER JOIN would look like:

SELECT *
FROM nba_player p LEFT OUTER JOIN  shoe_company s
                                       ON (p.shoecompany_id = s.id)

To generate the OUTER JOIN query using JPQL, you’ll have to write this:

SELECT p FROM Player p LEFT JOIN p.shoeCompany sc

That’s it, now you’re free to use and abuse LEFT OUTER JOINs all you want. I’m using TopLink Essentials as the JPA implementation but that shouldn’t come into play. Grab the techy part of the blog’s feed.