Inserting Sub-Queries in SELECT Statements in Oracle - Sub-Queries returning single and multiple values in Oracle
(Page 2 of 6 )
Let us consider the following statement:
SELECT
*
FROM emp
WHERE sal = 800
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:
SELECT
*
FROM emp
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.
Next: Sub-Queries as part of the BETWEEN operator in Oracle >>
More Oracle Articles
More By Jagadish Chatarji