HomeOracle Page 6 - Inserting Sub-Queries in SELECT Statements in Oracle
Sub-Queries as (or part of) columns in Oracle SELECT statements - 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.
Before getting into sub-queries as part of columns, let us look at the following small query:
SELECT MAX(sal) AS highest, MIN(sal) AS least, COUN(*) AS employees, SUM(sal) AS total FROM emp
Everyone can easily understand that the above query returns only one row containing four values of aggregation. Let us rewrite the above query using sub-queries to get the same output.
SELECT (SELECT MAX(sal) FROM emp) AS highest, (SELECT MIN(sal) FROM emp) AS least, (SELECT COUNT(*) FROM emp) AS employees, (SELECT SUM(sal) FROM emp) AS total FROM dual
You can observe that I replaced all aggregate functions with sub-queries! Another important issue to concentrate on in the above query is the “dual” table. As the sub-queries in the above statement are working individually by themselves, I need not work with any table and thus I used the “dual” table. If you want to learn more about the “dual” table, please go through my first article in this same series.
Now, let us look at an interesting query which deals with sub-queries at both the column level and the CASE level. The following is the query:
SELECT empno, ename, sal AS salary, ROUND((sal -(SELECT AVG(sal) FROM emp)),2) AS avgcompare, CASE WHEN sal >= (SELECT AVG(sal) FROM emp) THEN 'HIGH' ELSE 'LOW' END AS paying FROM emp
The following is the sample output of the above query:
EMPNO ENAME SALARY AVGCOMPARE PAYING
------- ---------- -------- ------------ ------
7839 KING 5000 2926.79 HIGH
7698 BLAKE 2850 776.79 HIGH
7654 MARTIN 1250 -823.21 LOW
7499 ALLEN 1600 -473.21 LOW
Any bugs, doubts, suggestions, feedback etc. are highly appreciated at http://jagchat.spaces.live.com