Stepping through Sub-Queries in Oracle - A sub-query with aggregate functions (or group functions) in Oracle
(Page 3 of 5 )
I already introduced sub-queries in the previous section. In this section, I shall start giving you some practical examples.
Let us consider that I would like to retrieve the details of the highest paid employee. Let us write the question in a meaningful manner and identify the steps as follows:
From the above figure, you have two steps to work with for the query. The following is the order you must follow (based on the above figure):
- Find the highest salary from the table (1)
- Based on the value you get, retrieve the employee details like empno, ename, etc. belonging to that salary. (2)
The following is the statement which retrieves the highest salary:
SELECT MAX(sal) FROM emp
To retrieve an employee's details based on a given salary (say 5000), the query would be as follows:
SELECT empno,ename,sal,deptno FROM emp
WHERE sal = 5000
Just replace the value 5000 with the query that gives you the highest salary. The complete statement would be as follows:
SQL> SELECT empno,ename,sal,deptno FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp);
Now, let us walk through its execution:
- The innermost query gets executed first.
- In this case, the query “select max(sal) from emp” gets executed first. It retrieves the highest value in the column “sal” from the table “emp.”
- Once the innermost query gets executed, it returns a value to the immediate outer query. In this case, it is 5000.
- The entire innermost query gets replaced with the new value returned by it. In this case, the outer query virtually becomes “select empno, ename, sal, deptno from emp where sal = 5000.”
- And finally, the outer query gets executed, which retrieves KING’s details.
Let us end this section with a final touch. Why can’t I write the above query as follows?
SQL> SELECT empno,ename,sal,deptno FROM emp
WHERE sal = MAX(sal)
Or even the following:
SQL> SELECT empno,ename,sal,deptno FROM emp
WHERE MAX(sal) = sal
None of the above two queries get executed successfully. The reason is that a condition in a WHERE clause cannot contain any aggregate function (or group function) without a sub-query!
Next: Designing sub-queries to deal with more than one table (or different tables) >>
More Oracle Articles
More By Jagadish Chatarji
