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;