Module 2: Logical Query Processing Order

Previous Tutorial - Next Tutorial

Now try to think of the order in which the request needs to be logically interpreted to write the correct T-SQL code, You should first understand a concept called logical query processing. Now you’re very much aware that in T-SQL.

The main statement used to retrieve the data is the SELECT statement. Therefore anybody can think of the logical query processing in the order that you are supposed to type them which looks something like this.

So this should be the expected query processing. But the actual processing order is very different. The logical processing of a query must first know which table is being queried before, it can know which columns can be returned from the table.

So let’s understand each phase of the logical query processing order of the main query clauses. It starts with the FROM clause, in this phase, you will indicate the tables you want to query and table operators like join if applicable. If you need to query just one table you indicate the table name as the input table in this clause.

Here each phase returned some output which is given to the next phase as input. So this second phase is filtering the rows based on the predicate in the WHERE clause. It will return only those rows for which the predicate evaluates to True. The phase is applicable only if the query hasn’t WHERE clause. So in short if the WHERE clause is not specified in the T-SQL then this phase is not evaluated. So whatever result in it will return from this phase and provided as input to the next one.

The third one is GROUP BY, this phase defines a group for each distinct combination of values in the grouped elements. We will understand it practically in detail later. This phase is applicable only if the query hasn’t GROUP BY clause. The next one is the HAVING clause.

This phase is also responsible for filtering data based on a predicate specified in this clause but it is evaluated after the data has been grouped. Hence it is evaluated per group and filters group as a whole because it acts on the result of the GROUP BY clause, aggregation function can be used in the having clause predicate.

This phrase is applicable only if the query has a HAVING clause. So here as the beginners no need to confuse the WHERE clause and HAVING clause because the WHERE clause is evaluated before the rows are grouped and the WHERE clause is evaluated per row while the HAVING clause is evaluated per group and it is evaluated after the rows are grouped.

The next phase is to SELECT. This phase is evaluated the expression in the select list and producing the result attributes. Now interesting point to be noted here is that in the logical query processing order it gets evaluated almost at last. But in the query, the select statement appears first.

The result returned in this phase is rational. It means it may be ordered in a particular order or it may not. So ORDER BY is the clause that guarantees the order in the result. So the next phase which is evaluated is the ORDER BY clause.

This phase is responsible for returning the result in a specific order according to the expression that appears in the order by list. This phase again is applicable only if the query has an ORDER BY clause. Now the next one is the TOP(n) clause. This phase is responsible to limit the number of records to return.

Now here the point to note is this clause is not supported by all SQL databases. Now let’s jump into the code. First, let us get started with a WHERE clause and then we will see the different operators used with this WHERE clause.

The “Logical Query Processing” is the most important concept of SQL. In SQL, a SELECT statement describes how a query should be executed and the ultimate output produced.

SQL has both logical and physical sections. The logical view is the mental concepts’ analysis of the query that describes the precise result of the query. The physical view is the execution of the question by the database engine. Physical execution must return the outcome determined by logical query processing. To reach this goal, the engine can implement optimization. Optimization can reset levels from logical query processing or switch levels completely—but only as long as the output is properly achieved and described by logical query processing.

For this purpose, it is necessary not to form any performance-related inferences from what users discover regarding logical query processing. That’s because logical query processing only establishes the accuracy of the query. When discussing the performance characters of the query, users need to learn how optimization acts. As stated, optimization can be quite distinct from logical query processing because it’s enabled to modify things as long as the outcome delivered is the one described by logical query processing.

It’s important to understand that the official SQL wasn’t first called so; rather, it was described SEQUEL. But then because of a trademark conflict, the language was named SQL, for “structured query language.” One should always remember that users must give their commands in an English-like manner. Because SQL is based essentially on the relational model—a denotative model describing data that was built by Edgar F. Codd in 1969. The relational model is based on two principal analytical sections: set theory and predicate logic. Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions