A subquery (sub-query) is a SELECT statement in the SELECT-, FROM-, WHERE- or HAVING-clause of another SELECT statement.
Note: A SELECT subquery in the SELECT-clause is very seldom needed and most often misused leading to bad performances as this subquery is executed for each row returned by the main query.
Subquery executes first and feeds output into the main query:
SELECT ename, deptno FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR');
Correlated subquery (both executes simultaneously):
SELECT ename, deptno, sal FROM emp x WHERE sal > (SELECT AVG(sal) FROM emp WHERE emp.deptno = x.deptno) ORDER BY deptno;
|Glossary of Terms