Sub-query

From Oracle FAQ
Jump to: navigation, search

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

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

  • 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 #