Inserting Sub-Queries in SELECT Statements in Oracle - Sub-Queries as part of the BETWEEN operator in Oracle

(Page 3 of 6 )

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!

Next: Derived tables (or inline views) with Sub-Queries in Oracle >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Inserting Sub-Queries in SELECT Statements in Oracle

· Hello guys! This is my second contribution on working with Sub-Queries in Oracle. ...   >>> Post your comment now!