HomeOracle Page 2 - Inserting Sub-Queries in SELECT Statements in Oracle
Sub-Queries returning single and multiple values in Oracle - Oracle
This is the second article in a series concentrating on working with sub-queries in Oracle. My previous article (first in this series) explained the “dual” table in detail and gave a few practical examples of sub-queries. In this article, I shall introduce sub-queries at various interesting places within the same SELECT statement.
When you execute the above statement, the condition in the WHERE clause works with only a single value. Let us rewrite the above statement with a plain sub-query as follows:
SELECT * FROM emp WHERE sal = (SELECT MIN(sal) FROM emp)
From the above statement you can understand that the sub-query returns one and only one value (or single value) and the query works fine.
If we would like to work with more than one value in a condition (of equality), you may need to work something like the following:
SELECT * FROM emp WHERE empno IN (7902,7788)
Let us frame the above with a sub-query as follows:
WHERE empno IN
(SELECT empno FROM emp
WHERE sal = (SELECT MAX(sal) FROM EMP
WHERE sal < (SELECT MAX(sal) FROM emp)))
The above query gives us all the employees earning the second highest salary! In the above case, the second level sub-query returns more than one value (or two employee numbers). Therefore, I provided the IN operator as part of the condition. If you replace the IN with “=” in the above query, it will return with an error stating “single row sub-query returns more than one row.” When you receive such an error, try to replace “=” with “IN” wherever appropriate.