Oracle plsql, select in another select

Subquery or nested query.

Related articles:

Oracle PL/SQL: Select in Another Select
Oracle PL/SQL is a powerful programming language that is widely used for developing applications that run on Oracle databases. One of the most useful features of PL/SQL is the ability to nest SQL queries within other SQL queries. This technique is known as a subquery or a select in another select.