title: SQL order of execution for correlated subquery
body: <p>I have the following <strong>Personnel table</strong>:</p>
  
  <pre><code>+---------+----------+-------------+
  | name    | dept_nbr | job_title   |
  +---------+----------+-------------+
  | Michael | 14       | Programmer  |
  | Kumar   | 14       | Programmer  |
  | Dave    | 14       | Programmer  |
  | Jane    | 14       | Manager     |
  | Carol   | 37       | Programmer  |
  | Joe     | 37       | Programmer  |
  | John    | 59       | CEO         |
  +---------+----------+-------------+
  </code></pre>
  
  <p><strong>Problem</strong>: Find all dept_nbr's (departments) that have fewer than 3 programmers.</p>
  
  <p><strong>Working query:</strong></p>
  
  <pre><code>SELECT DISTINCT dept_nbr
    FROM Personnel AS P1
   WHERE (SELECT COUNT(P2.dept_nbr)
            FROM Personnel AS P2
           WHERE P1.dept_nbr = P2.dept_nbr AND P2.job_title = 'Programmer') &lt; 3;
  </code></pre>
  
  <p><strong>Result:</strong></p>
  
  <p>37
  <br>
  59</p>
  
  <p><strong>Notes:</strong></p>
  
  <p>Department 14 is correctly not included as it has 3 programmers (3 is equal to but not fewer than 3).  Department 59 has zero programmers, and is also correctly included in the results.</p>
  
  <p><strong>My question:</strong></p>
  
  <p>When the above query executes, how does a generic SQL engine proceed?  From what I have read, SQL execution order is (roughly): From, Where, Group By, Having, and Select. So, is the following correct? </p>
  
  <p><strong>1 -</strong> The Outer Query passes each row of the Personnel table as P1 into the Inner query.</p>
  
  <p><strong>2.a -</strong> The Inner Query scans the entire Personnel table as P2, row by row, looking for rows that satisfy the condition "P1.dept_nbr = P2.dept_nbr AND P2.job_title = 'Programmer'".</p>
  
  <p><strong>2.b –</strong> Once the Inner Query is done with the entire table, it COUNTs the matching dept_nbr values and returns it to the Outer Query.</p>
  
  <p><strong>3 –</strong> In the Outer Query, if the count returned from the Inner Query satisfies the condition "WHERE (Inner Query Count Result) &lt; 3", the corresponding dept_nbr for the P1 row is SELECTed.</p>
  
  <p><strong>4 –</strong> Following all rows processed by the Outer Query, the Outer Query does a DISTINCT on the results and displays the unique dept_nbr values.</p>
  
  <p>Is my understanding above correct?  Specifically, does the outer query do the DISTINCT at the very end (step #4)?  It seems that in this way, the inner query does redundant scanning (for example, it processes dept_nbr = 14 four times, when it really has the answer in the first pass).</p>
  
  <p>I tested the above query on sqlfiddle.com w/ MySQL 5.6.</p>