Inserting Sub-Queries in SELECT Statements in Oracle (Page 1 of 6 )
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.
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!
Next: Sub-Queries returning single and multiple values in Oracle >>
More Oracle Articles
More By Jagadish Chatarji
/ 35 
