Oracle:Subqueries

Main Page >> Oracle and SQL >> Workbook >> Functions >> Subqueries

Subqueries

A subquery, or nested query is a query within a query. Subqueries are usually used in the WHERE part of the SQL statement, though you can also use nested expressions in place of column and table names, for example:

SELECT * FROM

(SELECT * FROM EMP);

Subqueries are often used where you need to perform a calculation, or a check of some sort, where the result is then passed to the outer query. Subqueries can be used with comparison operators such as =, <, >, >=, <= etc. If using equals, only one result must be returned from the subquery, if more than one value is returned then either the IN or NOT IN comparison operator must be used instead of equals.

For example, the outer join query that showed departments with no employees could be written as this instead:

SELECT DEPTNO, DNAME
FROM DEPT
WHERE DEPTNO NOT IN (SELECT DEPTNO FROM EMP);

Use a subquery to list all employees who are involved in a project:

SELECT  ENAME,  DEPTNO  FROM  EMP
WHERE  EMPNO IN     

(SELECT EMPNO FROM EMPPROJ);

A Group Function in a Subquery

If you include group functions in a SELECT command, you may not select individual results as well. For instance if you wanted to find the name of the employee who receives the highest salary, you cannot enter:

SELECT ENAME, MAX(SAL) .....

because this mixes individual and group functions. Instead you have to use a subquery:

SELECT  ENAME,  JOB,  SAL  FROM  EMP  
WHERE  SAL  =  
   (SELECT  MAX(SAL)  FROM  EMP);

Show the details of the latest employee to be hired by the company:

SELECT  ENAME,  JOB,  SAL  FROM  EMP   
WHERE  HIREDATE  =  

(SELECT MAX(HIREDATE) FROM EMP)

This should return the row you inserted for yourself in Exercise 3.2.

Next Step

Working withdate values.