Include null values using JPQL order by

Question

I'm developing a lazy loading model for a Java EE project. I've a problem when i try to sort my data which have a null values. For example,

SELECT u FROM Users u ORDER BY u.Countries.label ASC

This query show all the data sorted by Countries but the Users with out a Country are omitted. The matter is here :

1. i need to show them also.
2. I'm implementing a generic model = i don't want to enforce the queries.

SELECT u FROM Users u ORDER BY u.Countries ASC

This is also are not working, because i must specify a valid ORDER BY item like label.

Answer 1

You need to use an outer join,

SELECT t1 FROM Table1 t1 left join t1.column1 c1 ORDER BY c1.id ASC
OR    
SELECT * FROM Users ORDER BY Countries DESC NULLS LAST;

If you are using criteria API , you can use following

USer user = criteriaBuilder.asc(myExpression).nullsFirst();