Home arrow Oracle arrow Inserting Sub-Queries in SELECT Statements in Oracle

Inserting Sub-Queries in SELECT Statements in 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

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!



 
 
>>> 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: