Hql nested subquery

I. Concept of nested subqueries:
In SQL, a select-from-where statement becomes a query block. Nesting a query block in the WHERE clause of another query Block
In a condition of a having phrase, such a query is called a nested query or a subquery. For example:
From student s
Where S. SnO IN
(Select SnO from SC where CNO = '1 ')
The preceding hql statement generated in the hibernate background is:
Select
Student0 _. ID as id1 _,
Student0 _. Sno as sno1 _,
Student0 _. sname as sname1 _,
Student0 _. ssex as ssex1 _,
Student0 _. sdept as sdept1 _,
Student0 _. Sage as sage1 _,
Student0 _. saddress as saddress1 _
From
Joblog. Student student0 _
Where
Student0 _. SnO IN (
Select
SC1 _. SnO
From
Joblog. SC SC1 _
Where
SC1 _. CNO = '1'
)
In this example, the lower query block select SnO from SC where CNO = '1' is nested in the upper query Block
From student s where S. SnO IN where condition. The upper query block is also called the outer query or parent query,
The lower-level query block is called an inner-level query or subquery.
The nested query solution is handled from the inside out. Each subquery is queried before the previous query is processed,
The subquery results are used to create query conditions for the parent query.
Ii. subqueries with in predicates:
A subquery with an in predicate refers to the connection between the parent query and the subquery using the predicate in to determine whether the value of an attribute column is in the results of the subquery.
In nested queries, the results of subqueries are often a set.
For example, query students in the same department as "Zhang San. You can use the following method: first query the system where "Zhang San" is located,
Query all the students in this department. The first query is a subquery, followed by a parent query. The Code is as follows.
From student s
Where S. sdept in
(Select S. sdept from s where S. sname = 'zhangsan)
Iii. Comparison subquery:
If you know exactly that the subquery returns a single value, you can use the =,>, >=, <, <=, and <> comparison operators to compare subqueries.
For example, query the students in the same department as "Zhang San.
This example is the same as the above example. "Zhang San" can only be learned in one system. Therefore, the subquery returns a single value, which can be used to compare subqueries.
From student s
Where S. sdept =
(Select S. sdept from s where S. sname = 'zhang san ')
4. subqueries with any or all:
When any or all predicates are used, comparison operators must be used at the same time. Query the list of students younger than any student in other computer systems.
From student s
Where S. Sage <Any (select S. sage from s where S. sdept = 'computer system ')
And S. sdept <> 'computer System'
The subquery queries all the ages of computer students, and then uses the "<any" keyword for age comparison.
The condition s. sdept <> 'computer system' after and is the condition of the parent query.
The predicates of subqueries with any or all are described as follows.
> Any, which is greater than a value in the subquery result.
<Any, which is smaller than a value in the subquery.
> = Any, greater than or equal to a value in the subquery.
<= Any, which is less than or equal to a value in the subquery.
= Any, which is equal to a value in the subquery.
! = Any or <> Any, not equal to a value in the subquery.
> All, greater than all values in the subquery.
<All, less than all values in the subquery.
> = All, greater than or equal to all values in the subquery.
<= All, less than or equal to all values in the subquery.
= All, equal to all values in the subquery.
! = All or <> all, not equal to any value in the subquery.

Here, an hql statement is recorded:

From outerfilesbasic as O
Inner join fetch
O. outercosts as c Where C. costcol = '0' and C. endtime = to_date ('1970-01-02 ', 'yyyy-mm-dd') and O. ID in (select filesbasic from outerzcsb as Z where Z. declevel like '% % ')