Logical Query Processing

Important to understand for SQL interviews!

For the very first post of this newsletter, I wanted to talk about the logical processing order of a SQL query.

When I refer to logical query processing, I am talking about the conceptual way how the database engine will read a query.

We read and write queries like this:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

However, the database engine conceptually reads the query like this:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

This is what is meant by the order of logical query processing.

Now you might look at that order and think to yourself ‘What about JOINS, DISTINCT and TOP? Where do they fit in the order?’

Here is the extended version of the order of logical query processing:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

This is something useful to keep in mind. Interviewers love asking this at SQL interviews. They might test you on this topic by asking the question ‘Why can I use aliases from the SELECT clause in the ORDER BY clause but not in the WHERE, HAVING or GROUP BY clause?’. This is due to logical query processing.

WHERE. HAVING and GROUP BY are all processed before the SELECT clause. ORDER BY is processed after the SELECT clause, which is why the alias is recognised in the ORDER BY clause.

Let’s have a look at this in action.

Below I have a simple query:

SELECT first_name, last_name
FROM sales.customers

Below is a sample of the 1455 records returned by the above query:

Now I am going to join the columns together to derive a new column with an alias named ‘full_name’.

SELECT (first_name + ' ' + last_name) AS full_name
FROM sales.customers

These are the results of this query:

Here I will try and find the record with the name ‘Daryl Spence’ by using the alias ‘full_name’ in the WHERE clause.

SELECT (first_name + ' ' + last_name) AS full_name
FROM sales.customers
WHERE full_name = 'Daryl Spence'

I run into this error when I execute the above query:

Now we see that the database engine doesn’t recognise the column ‘full_name’ in the WHERE clause.

Let’s try using the same alias in an ORDER BY clause for the same query:

SELECT (first_name + ' ' + last_name) AS full_name
FROM sales.customers
ORDER BY full_name

This time the query didn’t run into an error and the database engine recognised the column ‘full_name’ and gave us these results:

The reason why the alias ‘full_name’ is recognised in the ORDER BY and not in the WHERE clause is due to the fact that the SELECT clause is processed after the WHERE clause and before the ORDER BY clause.

Hope this was informative to you guys and I can’t wait to share more SQL content with you!

Cheers! - Shashane

All the posts I made this week on Instagram (@sql.handbook), check if you missed anything:

SQL Interview question - What is the logical processing order of a SQL query? -

sql.handbook

A post shared by Shashane | Data Engineer | SQL (@sql.handbook)

Task - Finding the staff that work in the state of Texas

sql.handbook

A post shared by Shashane | Data Engineer | SQL (@sql.handbook)