What is a Logical SQL Query

A logical SQL query is a conceptual interpretation of what the result of a SQL query will be. The physical SQL query is the processing of the query by the database engine. Optimisation of the query is done physically by the engine, which must ensure that the value that results from the logical query must be the same.

Order of Logical Query Processing Phases:

Below is the order in which the query is processed logically, which is not similar to the order you type it in.

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

For example, let’s discuss the below query:

SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees
FROM HR.Employees
WHERE hiredate >= '20030101'
GROUP BY country, YEAR(hiredate)
HAVING COUNT(*) > 1
ORDER BY country , yearhired DESC;

If we look closely, you can see that the first line of the command asks to select the attributes that need to be imported from the table.

However, logically, the query will first process the FROM (line 3).

empid hiredate country
------ ----------- --------
1 2002-05-01 USA
2 2002-08-14 USA
3 2002-04-01 USA
4 2003-05-03 USA
5 2003-10-17 UK
6 2003-10-17 UK
7 2004-01-02 UK
8 2004-03-05 USA
9 2004-11-15 UK

Second, the predicate or the statement WHERE will be processed, which asks to select employees which were hired after 2003.

empid hiredate country
------ ----------- --------
4 2003-05-03 USA
5 2003-10-17 UK
6 2003-10-17 UK
7 2004-01-02 UK
8 2004-03-05 USA
9 2004-11-15 UK

Third, the data will be grouped by country and year of hire.

group group detail detail detail
country YEAR(hiredate) empid country hiredate
-------- -------------- ------ ------- ----------
UK 2003 5 UK 2003-10-17
6 UK 2003-10-17
UK 2004 7 UK 2004-01-02
9 UK 2004-11-15
USA 2003 4 USA 2003-05-03
USA 2004 8 USA 2004-03-05

Fourth, the HAVING part will be processed. This acts as a predicate for the grouped output.

group group detail detail detail
country YEAR(hiredate) empid country hiredate
-------- -------------- ------ ------- ----------
UK 2003 5 UK 2003-10-17
6 UK 2003-10-17
UK 2004 7 UK 2004-01-02
9 UK 2004-11-15

Next, the SELECT clause will be processed. This will select which attributes we want to use from the HR.Employees table. Note that if we try to use an alias, defined in a SELECT, in any of the other clauses, we will get an error.

country yearhired numemployees
-------- ---------- ------------
UK 2003 2
UK 2004 2

Until this point, the output is relational, and conforms to the Set Theory and Predicate. The output is unique, attributes have title and type, and the output is not in a definite order.

Finally, the ORDER BY clause is processed which will order the output based on country in ascending order (default), then on year hired, in descending order.

country yearhired numemployees
-------- ---------- ------------
UK 2004 2
UK 2003 2

What about the Physical SQL Query order of statements? 

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

Now you know the difference between the logical query and physical query processing order of statements.

If you are interested to learn more about SQLite database issues and solutions, we have a nice article to read.