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!