Inserting Sub-Queries in SELECT Statements in Oracle

All the queries in this series have been tested only with Oracle 10g (V10.2). A few of the features may not be available in previous versions. For a detailed listing of features, please go through the Oracle documentation.

Sub-Queries with multiple columns in Oracle

Let us try to work with the following query:

SELECT
*
FROM emp
WHERE (sal,mgr) = (3000,7566)

The above would never work and results in giving you the “invalid relational operator” error. This is because you can compare only one value at a time and not more than one. The following is a small trick to overcome the problem:

SELECT
*
FROM emp
WHERE (sal,mgr) = (SELECT 3000,7566 FROM dual)

I just made both of those two values part of the sub-query and it works fine!  Both of those values are not from any table, so I used “dual.”  Now you can observe the power of the “dual” table.  If you want to learn more about “dual,” please refer to my first article in this series.

Let us work with a more practical sub-query rather than with a “dual” table as follows:

SELECT
*
FROM emp
WHERE (sal,mgr) =
(SELECT sal,mgr FROM emp
             WHERE sal = (SELECT MIN(sal) FROM EMP
                          WHERE sal > (SELECT MIN(sal) FROM
emp)))

The above returns all the employees who are earning the same salary and working under the same manager of the employee earning the second least salary!