Oracle single row subquery

Oracle single row subquery

Subqueries can contain SELECT statements in SELECT, UPDATE, and DELETE statements. The internal SELECT statement result can be part of the Condition Clause in an external statement,
It can also be used as a temporary table for external queries. Subqueries are of the following types:
1. single-row subquery: Only one row of results is returned if no external results are returned.
2. multi-row subquery: returns zero rows, one row, or multiple rows to the outside.

Single Row subquery code Demo: query the employee name, job, and salary under the SALES Department (SALES. This problem can be achieved through join query. Because all the required results are in the Emp table,

You can first query the Department number corresponding to the sales department from the Dept table, and then query the employee records that match the department according to the current Department number in the Emp table.

The query results from the sales table can be used as the conditions for queries in the Emp table. 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 results of an internal query are used as conditions for an external query.

Note:
If no record is returned for an internal query, the DEPTNO and NULL fields in the external condition are always false, that is, the external query does not return any results.
You can use comparison operators such as =,>, <, >=, <=, and <> in a single row subquery.
The results returned by the internal query of distinct must match the field (DEPTNO) in the external query condition.
If the internal query returns multiple rows, an error occurs.