Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Inserting Sub-Queries in SELECT Statements in Oracle
  2. Sub-Queries returning single and multiple values in Oracle
  3. Sub-Queries as part of the BETWEEN operator in Oracle
  4. Derived tables (or inline views) with Sub-Queries in Oracle
  5. Sub-Queries with CASE structure in Oracle SELECT statements
  6. Sub-Queries as (or part of) columns in Oracle SELECT statements
By: Jagadish Chatarji
Rating: starstarstarstarstar / 62
November 14, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: