Logical Query Processing Phase

SQL is a declarative English-like language. In SQL you request the data needed and the database engine figures out how to process the requests.

Take a look at this SQL statement:

SELECT CustomerName, PhoneNumber
FROM Customers

This SQL statement states that the results should be a list of Customer’s names and phone numbers. The columns that should be displayed are listed first and then the table that the data should be pulled from is listed next. Microsoft SQL server processes the statement above in a different order then how it’s written.

To help you understand this assume that you have an excel document with the following data in it. The Excel document has two tabs named:

• Customers
  • With the following columns:
    • CustomerID
    • CustomerName
    • PhoneNumber
• Sales
  • With the following columns:
    • CustomerID
    • InvoiceID
    • SaleTotal

If someone asked for a list of Customer’s names and phone numbers what would be the steps that you would have to be take to get that information to them?

1. Open the Excel document
2. Select the Customers tab
3. Highlight the data in the Customer Name and Phone Number fields
4. Copy that data and send it the person that made the request

The first step taken once the document is open is to select the tab that has the data requested on it and then select the requested columns. Microsoft SQL server works the same way. First it selects the table that has the data that was requested in it and then the columns that were requested.

The order that Microsoft SQL Server processes the SQL clauses is called the Logical Query Processing phase. Each statement returns a virtual table that becomes the input for the next step.

Below is the order that Microsoft SQL server processed the SQL clauses in:

1. FROM clause plus any JOIN clauses

• ON
• OUTER
• At this point you cannot use any of the aliases that were use in the SELECT clause because it haven’t been processed yet

2. WHERE clause

• Once again aliases from the SELECT clause can’t be used yet because it hasn’t been processed yet

3. GROUP BY clause

• You still can’t use aliases from the SELECT clause because it hasn’t been processed yet

4. CUBE | ROLLUP

• Still can’t use aliases from the SELECT clause

5. HAVING clause

• Still can’t use aliases from the SELECT clause

6. SELECT

• If the SELECT clause has a statement like TOP it’s not processed at this point

7. DISTINCT
8. ORDER BY

• Now you can use aliases from the SELECT clause

9. TOP

• Now the top statement from the SELECT clause is processed

If you think about it this order makes sense logically. You can’t select data from specific columns until you pull the data from the table first.

So lets take a look at how an actually SQL statement would be processed:

SELECT COUNT(SaleID) AS NumberOfSales, CustomerID, FirstName + LastName As CustomerName
FROM Sales
WHERE SaleTotal > 100
GROUP BY CustomerID, FirstName, LastName
HAVING COUNT(SaleID) > 1

Here is how SQL server would process this statement:

1. Process the FROM clause (FROM Sales) and pulls back all the information from the Sales table:

Sale ID	CustomerID	FirstName	LastName	SaleTotal
1	1	Bob	Smith	101
2	1	Bob	Smith	200
3	2	Jessica	Jones	50
4	3	Sara	Jones	400
5	3	Sara	Jones	50
6	3	Sara	Jones	500
7	4	Jason	Thomas	500

2. Process the WHERE clause (WHERE SaleTotal > 100) pulling back only rows that have a SaleTotal greater then 100

Sale ID	CustomerID	FirstName	LastName	SaleTotal
1	1	Bob	Smith	101
2	1	Bob	Smith	200
4	3	Sara	Jones	400
6	3	Sara	Jones	500
7	4	Jason	Thomas	500

3. Process the GROUP BY clause (GROUP BY CustomerID, FirstName, LastName) so we can get a count of the number of sales each customer has made.

NumberofSales	CustomerID	FirstName	LastName
2	1	Bob	Smith
2	3	Sara	Jones
1	4	Jason	Thoma

4. Process the HAVING clause (HAVING COUNT(SaleID) > 1) to only pull back Customers that have more then one Sale

NumberofSales	CustomerID	FirstName	LastName
2	1	Bob	Smith
2	3	Sara	Jones

5. Process the SELECT clause (SELECT COUNT(SaleID) AS NumberOfSales, CustomerID, FirstName + LastName As CustomerName)

NumberofSales	CustomerID	CustomerName
2	3	Sara Jones
2	1	Bob Smith

5. Process the ORDER BY statement (ORDER BY CustomerID DESC) pulling up the newest customer first

NumberofSales	CustomerID	CustomerName
2	1	Bob Smith
2	3	Sara Jones