[SQL] Logical Query Processing Phases

Sample query:

SELECT C.customerid, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON C.customerid = O.customerid
WHERE C.city = 'Madrid'
GROUP BY C.customerid
HAVING COUNT(O.orderid) < 3
ORDER BY numorders;

Processing phases

1. FROM phase
   
2. WHERE phase
   
3. GROUP BY phase
   
4. HAVING phase
   
5. SELECT phase
   
6. ORDER BY phase

FROM phase
The FROM phase identifies the table or tables that need to be queried. This phase involves three subphases in a JOIN: (1) cartesian product, (2) ON filter, and (3) add outer rows.

WHERE phase
The main difference between the ON filter and in the WHERE filter is that ON is applied before adding outer rows (3), while WHERE is applied afterwards.

GROUP BY phase
A query that contains a GROUP BY clause will generate one row per group.

HAVING phase
The HAVING filter is the only filter that applies to the grouped data.

SELECT phase
SELECT phase constructs the table that will eventually be returned to the caller. This phase involves three subphases: (1) evaluate expressions, (2) apply DISTINCT clause, (3) apply TOP option.

ORDER BY phase
The rows from the previous step are sorted according to the column list specified in the ORDER BY clause, returning the cursor.

Reference: Itzik Ben-Gan, Inside Microsoft® SQL Server® 2008: T-SQL Querying.