Logical Query Processing in SQL Server

To learn the basics of SQL Server, you must have an understanding of how an SQL Server query engine executes an SQL query. T-SQL involves both logical and physical query processing. Physical query processing is about how a database engine processes a query. Logical query processing is a conceptual flow, an order of the clauses knowing how SQL Server evaluates these clauses while executing a query.

Most programming languages follow the fundamental of processing a program line by line, but SQL server has a defined logical query processing order to execute queries.

The logical processing order has been split into two different branches based on whether UNION is included or not.

Logical Query Execution in SQL Server Without a UNION Clause

1. FROM, JOIN, APPLY and ON: Join conditions are evaluated, and then the query filter ON is applied.

2. WHERE: This is another query filter applied to fetch records which match the WHERE conditions filter.

3. GROUP BY and Aggregate Functions: Grouping and Aggregations operations performed

4. HAVING: This is the Third filter applied to an aggregate result obtained from the group by to filter out the groups

5. SELECT: List of columns to be returned by query result

6. DISTINCT: To remove duplicate records

7. ORDER BY: Sorting the result Ascending/ Descending

8. TOP: TOP filter is applied to select defined X number of rows

9. FOR XML: To return query results as XML format

Logical Query Execution in SQL Server with a UNION Clause

1. FROM, JOIN, APPLY and ON: Join conditions are evaluated, and then the query filter ON is applied.

2. WHERE: Another query filter applied to fetch records which match WHERE conditions filter

3. GROUP BY and Aggregate Functions: Grouping and Aggregations operations performed

4. HAVING: The Third filter, applied to an aggregate result obtained from the group by to filter out the groups

5. TOP: The TOP filter is applied to select defined X number of rows.

6. SELECT and UNION: To combine two query result set and return it using SELECT statement

7. DISTINCT: To remove duplicate records

8. ORDER BY: Sorting the result Ascending/Descending

9. FOR XML: To return query results as XML format

Examples to Demonstrate Logical Query Processing Order

IF OBJECT_ID ('Employee' ) IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
 Id INT NOT NULL IDENTITY ( 1,1 ) PRIMARY KEY
,FirstName VARCHAR (50) NOT NULL
,MiddleName VARCHAR (50) NOT NULL
,LastName VARCHAR (50) NOT NULL
,ContactNo VARCHAR (10) NOT NULL
,Salary INT NULL
);

Insert into Employee values ( 'Vishwanath', 'D', 'D', '9999955555' , 12000);
Insert into Employee values ( 'Niraj', 'Y', 'Y', '9911223344' , 14000 );
Insert into Employee values ( 'Chetan', 'V', 'G' , '989898989', 700000);
Insert into Employee values ( 'Atul', 'K', 'K', '9876780987' , 40000);
Insert into Employee values ( 'Vishal', 'M', 'P', '7777711111', 12000 );

1.WHERE Clause Evaluated before SELECT

Often developers who do not understand logical query processing make this mistake: They try to use a column alias defined in a SELECT clause in a WHERE clause. This is not allowed because a SELECT clause is evaluated after a WHERE clause, so the column alias is not known to the WHERE clause. It raises an error of “Invalid column name.”

To solve this issue, you can use the derived table concept or (Salary * 12) in the where clause (which is actually not a good practice considering performance optimization).

SELECT Id, (Salary * 12) as YearlySalary
FROM    Employee
WHERE   YearlySalary > 10000;

Msg 207, Level 16, State 1, Line 3
Invalid column name ‘YearlySalary’.

2.WHERE Clause Evaluated before HAVING

The most important difference to distinguish between the WHERE and HAVING clause is the WHERE clause is evaluated before the HAVING clause. The WHERE clause is applied to rows and the HAVING clause is applied to groups created using the GROUP BY clause.

Let’s try to find departments from the employee table having a Sum of salary that is greater than 300000. The following query gives us a result, but it is not fulfilling the query requirement. We have added a filter in the WHERE clause which is filtering employees with a salary greater than 300000, not departments.

These are wrong attempts to filter out departments whose salary sum is greater than 300000.

SELECT DeptId, SUM( Salary ) as SumOfSalary
FROM   Employee
WHERE Salary > 300000
GROUP BY DeptId;

DeptId    SumOfSalary
2          700000

Right Query with filtering out with HAVING clause.

SELECT DeptId, SUM( Salary ) as SumOfSalary
FROM   Employee
GROUP BY DeptId
HAVING   SUM ( Salary) > 300000 ;

DeptId    SumOfSalary
2         752000

3.Referring Column Alias in a SELECT Clause

Column aliases are not visible to another expression in the same SELECT clause. It raises an error “Invalid column name YearlySalary” because we are trying to refer the alias created in the same select list. The reason being T-SQL evaluates all expressions that appear in the same logical query processing phase in an all-at-once manner.

SELECT (Salary * 12) as YearlySalary, YearlySalary / 100
FROM   Employee;

Msg 207, Level 16, State 1, Line 13
Invalid column name ‘YearlySalary’.

4.Referring Column Alias in ORDER BY Clause

When we create a column alias in a SELECT clause and try to sort the result based on the created column alias name, it is allowed because as per logical query processing, a SELECT clause is evaluated before an ORDER BY clause.

SELECT (Salary * 12) as YearlySalary
FROM   Employee
ORDER BY YearlySalary DESC;

YearlySalary

8400000
480000
168000
144000
144000

Read more about Database and SQL programming from Tech-Recipes.