Oracle Single-line subquery

A subquery can present a SELECT statement inside a SELECT, UPDATE, DELETE statement. The result of the internal SELECT statement can be used as part of the conditional clause in the outer statement.
It can also be used as a temporary table for external queries. The types of subqueries are:
1. Single-line subquery: No results are returned to the outside, or only one row of results is returned.
2. Multiline subquery: Returns 0 rows, one row, or more rows of results to the outside.

Single-Line subquery code Demo: Query the employee's name, job, and salary below the sales department. The problem can be implemented with a join query, because the required result information is in the EMP table,

You can find out the department number of the sales department from the Dept table, and then find the employee records in the EMP table according to the current department number.

The results from the sales table can be used as criteria for the query in the EMP table, and the SQL statement is implemented as follows:

Sql> Select Ename,job,sal from EMP   2  where deptno= (SELECT DEPTNO from DEPT where dname= ' SALES ')  3  /

Code parsing:
The result of an internal query as a condition for an external query.

Need to note:
 If the internal query does not return any records, the field deptno and Null in the external condition are always false, i.e. the external query does not return any results.
 External queries in a single-row subquery can use comparison operators such as =, >, <, >=, <=, <>, and so on.
 The results returned by the internal query must match the fields in the external query criteria (DEPTNO).
 An error occurs if the internal query returns multiple rows of results.

Oracle Single-line subquery