Sub-query

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.

Examples

Example subqueries:

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;

Also see

• Query - a SELECT statement
• Inline view - don't confuse subqueries with inline views!

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#