Subquery in FROM clause

Subqueries can be used in the FROM clause in place of table references.

Examples

SELECT * from (SELECT * FROM emp where sal > 10000) new_emp, dept 
WHERE new_emp.deptno = dept.deptno
SELECT * from (SELECT empno FROM emp)

Limitations of syntax in nested subquery

1. GROUP BY usage is not always support in nested subquery:

   SELECT newemp.deptno, dept.dname FROM (SELECT deptno, COUNT(*) AS empcount

   FROM emp GROUP BY deptno) newemp, dept

   WHERE newemp.deptno = dept.deptno;

2. SET functions in subquery are not always supported:

   SELECT * FROM (SELECT deptno, count(*) AS empcount

   FROM emp GROUP BY deptno) newemp

   WHERE empcount > 2;