JOINS via Hibernate JPA, PostgreSQL and Spring MVC

Let's suppose the following one-to-many relationship:

INNER JOIN

SELECT columns FROM TABLE_A a INNER JOIN TABLE_B b ON a.pk=b.pk

·         All players from all tournaments (players that participate in tournaments)

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b INNER JOIN b.players a

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

inner join players players1_ on tournament0_.id=players1_.tournament_id

"],

Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a INNER JOIN tournaments b ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

·         All tournaments for all players (tournaments that have players)

JPQL

SELECT a.playername, b.tournamentname

FROM Player a INNER JOIN a.tournament b

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                 tournament1_.tournament_name as col_1_0_

 from players player0_

inner join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b INNER JOIN players a ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

·         All players that participate to Wimbledon tournament and have their rank smaller or equal to 5

JPQL

SELECT a.playername, a.rank

FROM Tournament b INNER JOIN b.players a

WHERE b.tournamentname = :tournament AND a.rank <= :rank

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                players1_.player_rank as col_1_0_

from tournaments tournament0_

inner join players players1_ on tournament0_.id=players1_.tournament_id

where tournament0_.tournament_name=? and players1_.player_rank<=?

"],

 Params:[(1=Wimbledon,2=5)]

Native

SELECT a.player_name, a.player_rank

FROM players a INNER JOIN tournaments b ON b.id=a.tournament_id

WHERE b.tournament_name = :tournament AND a.player_rank <= :rank

Output:

Row: [Andy Murray, 3]

·         All tournaments that have players with rank smaller or equal to 5

JPQL

SELECT b.id, b.tournamentname

FROM Player a INNER JOIN a.tournament b

WHERE a.rank <= :rank

Hibernate (from JPQL)

Query:["

select     tournament1_.id as col_0_0_,

                tournament1_.tournament_name as col_1_0_

from players player0_

inner join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

 where player0_.player_rank<=?

"],

 Params:[(1=5)]

Native

SELECT b.id, b.tournament_name

FROM tournaments b INNER JOIN players a ON b.id = a.tournament_id

WHERE a.player_rank <= :rank

Output:

Row: [1, Roland Garros]

Row: [1, Roland Garros]

Row: [2, Wimbledon]

LEFT JOIN

SELECT columns FROM TABLE_A a LEFT JOIN TABLE_B b ON a.pk=b.pk

·         All players even if they are not in tournaments

JPQL

SELECT a.playername, b.tournamentname

FROM Player a LEFT JOIN a.tournament b

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                 tournament1_.tournament_name as col_1_0_

from players player0_

left outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a LEFT JOIN tournaments b ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

·         All tournaments even if they don't have players

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b LEFT JOIN b.players a

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

left outer join players players1_ on tournament0_.id=players1_.tournament_id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b LEFT JOIN players a ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

RIGHT JOIN

SELECT columns FROM TABLE_A a RIGHT JOIN TABLE_B b ON a.pk=b.pk

·         All tournaments even if they don't have players

JPQL

SELECT a.playername, b.tournamentname

FROM Player a RIGHT JOIN a.tournament b

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                tournament1_.tournament_name as col_1_0_

from players player0_

right outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a RIGHT JOIN tournaments b ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

·         All players even if they are not in tournaments

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b RIGHT JOIN b.players a

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                 tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

right outer join players players1_ on tournament0_.id=players1_.tournament_id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b RIGHT JOIN players a ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

FULL JOIN

SELECT columns FROM TABLE_A a FULL OUTER JOIN TABLE_B b ON a.pk=b.pk

·         All players and tournaments

JPQL

SELECT a.playername, b.tournamentname

FROM Player a FULL JOIN a.tournament b

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                tournament1_.tournament_name as col_1_0_

from players player0_

full outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a FULL JOIN tournaments b ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

·         All tournaments and players

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b FULL JOIN b.players a

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

full outer join players players1_ on tournament0_.id=players1_.tournament_id

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b FULL JOIN players a ON b.id = a.tournament_id

Output:

Row: [Rafael Nadal, Roland Garros]

Row: [Roger Federer, Roland Garros]

Row: [David Ferer, Roland Garros]

Row: [Kei Nishikori, BCR ]

Row: [Marin Cilic, BCR ]

Row: [Tommy Haas, Wimbledon]

Row: [Andy Murray, Wimbledon]

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

LEFT EXCLUDING JOIN

SELECT columns FROM TABLE_A a LEFT JOIN TABLE_B b ON a.pk=b.pk WHERE b.pk IS NULL

·         All players that are not in tournaments

JPQL

SELECT a.playername, b.tournamentname

FROM Player a LEFT JOIN a.tournament b WHERE b.id IS NULL

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                tournament1_.tournament_name as col_1_0_

from players player0_

left outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

where tournament1_.id is null

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a LEFT JOIN tournaments b ON b.id = a.tournament_id WHERE b.id IS NULL

Output:

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

·         All tournaments that don't have players

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b LEFT JOIN b.players a WHERE a.id IS NULL

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

left outer join players players1_ on tournament0_.id=players1_.tournament_id

where players1_.id is null

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b LEFT JOIN players a ON b.id = a.tournament_id WHERE a.id IS NULL

Output:

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

RIGHT EXCLUDING JOIN

SELECT columns FROM TABLE_A a RIGHT JOIN TABLE_B b ON a.pk=b.pk WHERE a.pk IS NULL

·         All tournaments that don't have players

JPQL

SELECT a.playername, b.tournamentname

FROM Player a RIGHT JOIN a.tournament b WHERE a.id IS NULL

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                tournament1_.tournament_name as col_1_0_

from players player0_

right outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

where player0_.id is null

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a RIGHT JOIN tournaments b ON b.id = a.tournament_id WHERE a.id IS NULL

Output:

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

·         All players that are not in tournaments

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b RIGHT JOIN b.players a WHERE b.id IS NULL

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                 tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

right outer join players players1_ on tournament0_.id=players1_.tournament_id

where tournament0_.id is null

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b RIGHT JOIN players a ON b.id = a.tournament_id WHERE b.id IS NULL

Output:

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

FULL OUTER EXCLUDING JOIN

SELECT columns FROM TABLE_A a FULL OUTER JOIN TABLE_B b ON a.pk=b.pk WHERE a.pk IS NULL OR b.pk IS NULL

·         All tournaments that don't have players and all players that don't participate in tournaments

JPQL

SELECT a.playername, b.tournamentname

FROM Player a FULL JOIN a.tournament b WHERE a.id IS NULL OR b.id IS NULL

Hibernate (from JPQL)

Query:["

select     player0_.player_name as col_0_0_,

                tournament1_.tournament_name as col_1_0_

from players player0_

full outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id

where player0_.id is null or tournament1_.id is null

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM players a FULL JOIN tournaments b ON b.id = a.tournament_id WHERE a.id IS NULL OR b.id IS NULL

Output:

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

·         Or, reverse fetch

JPQL

SELECT a.playername, b.tournamentname

FROM Tournament b FULL JOIN b.players a WHERE a.id IS NULL OR b.id IS NULL

Hibernate (from JPQL)

Query:["

select     players1_.player_name as col_0_0_,

                tournament0_.tournament_name as col_1_0_

from tournaments tournament0_

full outer join players players1_ on tournament0_.id=players1_.tournament_id

where players1_.id is null or tournament0_.id is null

"],

 Params:[()]

Native

SELECT a.player_name, b.tournament_name

FROM tournaments b FULL JOIN players a ON b.id = a.tournament_id WHERE a.id IS NULL OR b.id IS NULL

Output:

Row: [Richard Gasquet, null]

Row: [Steve Johnson, null]

Row: [null, Madrid Open]

Row: [null, Australian Open]

Row: [null, US Open]

The complete application is available here.