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

Sub-Queries as part of the BETWEEN operator 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

BETWEEN is a unique operator in Oracle.  Let me show you a small example of this:

SELECT
*
FROM emp
WHERE sal BETWEEN 1000 and 3000

It is very easy to understand the above query as it returns all the employees who earn salaries between 1000 and 3000 (both inclusive). Let us rewrite the above query as follows:

SELECT
*
FROM emp
WHERE sal BETWEEN
(SELECT 1000 FROM dual) AND 3000

I just replaced 1000 with a simple sub-query using “dual.” If you are new to the “dual” table, please refer to my first article in this series. Let us work a bit more practically as follows:

SELECT
*
FROM emp
WHERE sal BETWEEN 
    (SELECT MIN(sal) FROM emp) and 2000

We can even work a bit differently.  Let us go through the following query:

SELECT
Grade
FROM salgrade
WHERE 
    (SELECT MAX(sal) FROM emp)
    BETWEEN losal AND hisal

The above gives you the salary grade of the highest 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: