Sub-Queries returning single and multiple values in Oracle - Oracle

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.