T-SQL 101: 89 Logical order of T-SQL SELECT queries

It’s unfortunate that the SELECT query in SQL isn’t written in the order that operations logically occur (if not physically). I suspect that’s one of the things that makes learning SQL a bit harder than it needs to be.

Without getting into really complex queries, you need to understand the logical order of the operations.

FROM

The starting point is to determine where the data is coming from. This is normally a table but it could be other sets of rows like views or table expressions.

WHERE

This clause is the initial filtering of the data that was retrieved in the FROM clause. This is all about finding the rows that need to be considered in the query.

GROUP BY

This clause is about summarizing or aggregating the rows. We determine the columns that will be used for grouping. The SELECT column will normally later have these grouping columns so that you can make sense of the output. The SELECT might also have other columns but only if they are aggregated via MIN, MAX, AVG, etc.

HAVING

Newcomers to SQL often get confused about the purpose of the HAVING clause, particularly when comparing it to the WHERE clause. The HAVING clause is a filter applied to groups of data that have come from the GROUOP BY clause, where the WHERE clause is filtering rows of data.

SELECT

The SELECT clause is about deciding which columns or aggregates need to be returned. Formally it’s often called a projection of the data. Columns or aggregates can also be aliased at this point (i.e. given another name). If you notice how late in the logical order the SELECT clause is, you might realize that this is why an alias can be used later in an ORDER BY but not in any of the previous clauses.

ORDER BY

SQL queries don’t have any default order. If you want data returned in a specific order, then you need to specify the columns or aggregates that you want to order the output by.

It’s really important you start to have this picture in your head of the order that queries are logically sequenced.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-09-28