Search code examples
javahibernatesubqueryhql

Can HQL Select on the result set of another query?

Can HQL Select on the result set of another query? For example:

SELECT COUNT(*) FROM (SELECT * FROM Table)

I can do it in SQL but when I tried like above in HQL, it just showed me syntax error "unexpected token: ( near line 1, column 22 ..."

Solution

  • HQL does support subqueries, however they can only occur in the select or the where clause. The example you provide would best be wrote as a straight statement in HQL. For example:

    select count(*) from table t  (where table is the entity name)

    If the query involves a more complicated statement than (select * from Table), I would recommend putting this logic into a view and then creating an entity based off of this view.

    For databases that support subselects, Hibernate supports subqueries within queries. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed.

    Example

    from DomesticCat as cat
where cat.name not in (
    select name.nickName from Name as name
)