Oracle Sql: WITH Clause - subquery factoring clause

The WITH clause formally known as the subquery factoring clause.
The clause precedes the SELECT statement of a query and starts with the keyword “WITH.”
The WITH is followed by the subquery definition and a label for the result set.

Using the SCOTT schema, for each employee we want to know how many other people are in their department.

Using an inline view we might do the following.

SELECT
    DNAME, ECOUNT
FROM
    DEPT,
    (
        SELECT DEPTNO, COUNT(1) ECOUNT
        FROM EMP
        GROUP BY DEPTNO
    ) EMP_COUNT
WHERE
    EMP_COUNT.DEPTNO=EMP.DEPTNO

Using a WITH clause this would look like the following.

WITH EMP_COUNT AS
(
    SELECT DEPTNO, COUNT(1) ECOUNT
    FROM EMP
    GROUP BY DEPTNO
)
SELECT
    DNAME, ECOUNT
FROM
    DEPT, EMP_COUNT
WHERE
    EMP_COUNT.DEPTNO=EMP.DEPTNO



Related Links
- Recursive Subquery Factoring: With Clause
- SQL Interview Question Answers
- Oracle SQL Puzzles