title: How does the WITH clause know which table to work with?
body: <p>I came across the following SQL statement in a book:</p>
  <pre><code>WITH dcount AS
  (
      SELECT deptno, COUNT(*) AS dcount
      FROM employees
      GROUP BY deptno
  )
  SELECT 
      e.lname EMP_lastname,
      e.deptno e_dept,
      d1.dcount edept_count,
      m.lname manager_name,
      m.deptno mdept,
      d2.dcount mdept_count
  FROM 
      employees e,
      dcount d1,
      employees m,
      dcount d2
  WHERE 
      e.deptno = d1.deptno
      AND e.mgr = m.empno
      AND m.deptno = d2.deptno
      AND e.mgr = 7839
  </code></pre>
  <p>The purpose of the statement is to display an employees name, the department they work in, the number of coworkers in the department, the manager's name, and the number of people in the manager's department.</p>
  <p>The part that confuses me is where it says d1.count and d2.count. How is it counting different things if the count is based on the same WITH statement?</p>