Sub-query

From Oracle FAQ
Jump to: navigation, search

A subquery (sub-query) is a SELECT statement in the WHERE- or HAVING-clause of another SELECT statement.

[edit] 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;

[edit] 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 #