Using WITH clause

Home Forums Technical Discussions Using WITH clause

This topic contains 1 reply, has 1 voice, and was last updated by  anantha1987 4 years, 1 month ago.

  • Author
    Posts
  • #650

    anantha1987
    Participant

    Oracle with clause can me more useful when a sub query is being used many times in a query. Also it has a large performance benefit. See the below scenario.

    We have an employee table and has deptno, mgr, empno lets say, we need to know for each employee we want to know how many other employees are working with them.

    We can write a query using an inline view below.

    SELECT emp.ename AS employee_name,
    dci.dept_count AS emp_dept_count
    FROM employee emp,
    (SELECT deptno, COUNT(*) AS dept_count
    FROM employee
    GROUP BY deptno) dci
    WHERE emp.deptno = dci.deptno;

    Using a WITH clause you can make the query to look very simple and easy to understand.

    WITH dept_count AS (
    SELECT deptno, COUNT(*) AS dept_count
    FROM employee
    GROUP BY deptno) — You can make an inline view like this using a WITH clause
    SELECT emp.ename AS employee_name,
    dci.dept_count AS emp_dept_count
    FROM employee emp,
    dept_count dci — then you can use a inline view just created above like a normal table.
    WHERE emp.deptno = dci.deptno;

    But the above query makes not that much difference. But look at the below.

    What if we also want to pull back each employees manager name and the number of people in the managers department?

    SELECT e.ename AS employee_name,
    dc1.dept_count AS emp_dept_count,
    m.ename AS manager_name,
    dc2.dept_count AS mgr_dept_count
    FROM emp e,
    (SELECT deptno, COUNT(*) AS dept_count
    FROM emp
    GROUP BY deptno) dc1,
    emp m,
    (SELECT deptno, COUNT(*) AS dept_count
    FROM emp
    GROUP BY deptno) dc2
    WHERE e.deptno = dc1.deptno
    AND e.mgr = m.empno
    AND m.deptno = dc2.deptno;

    Using the WITH clause this would look like the following.

    WITH dept_count AS (
    SELECT deptno, COUNT(*) AS dept_count
    FROM emp
    GROUP BY deptno)
    SELECT e.ename AS employee_name,
    dc1.dept_count AS emp_dept_count,
    m.ename AS manager_name,
    dc2.dept_count AS mgr_dept_count
    FROM emp e,
    dept_count dc1,
    emp m,
    dept_count dc2
    WHERE e.deptno = dc1.deptno
    AND e.mgr = m.empno
    AND m.deptno = dc2.deptno;

    We don’t need to redefine the same sub query multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.

  • #652

    anantha1987
    Participant

    I am adding tags here

You must be logged in to reply to this topic.