800.443.5210
AAJ INSIGHTS

SQL Programming: Understanding the Logical Order of Operations in SELECT statements

Jim Gilbert
December 5, 2017

There are number of ways in which the most basic elements of SQL programming code can be constructed to ensure good processing performance. The best way to tune performance is to try to write your queries in a number of different ways and compare their reads and execution plans. Sometimes the reason for the slow response time is due to the fact that the query is not written to perform as efficiently as possible.

Following is the order in which SQL programming elements get executed as this will change the way we can optimize SELECT queries:

  1. FROM Clause
  2. ON
  3. OUTER
  4. WHERE
  5. GROUP BY
  6. HAVING
  7. SELECT
  8. DISTINCT
  9. ORDER BY
  10. TOP

 

In the SELECT statement, the FROM clause is the first element that will execute. This is the place where we can narrow down possible record set sizes by implementing appropriate ON conditions in JOINS. This way we have a smaller record set to handle for the WHERE clause and can increase the query processing performance.

The SELECT command allows use of a WHERE clause, reducing the amount of data read.  Again we can use this clause to filter down the record set and increase the performance of the query.  There are different types of comparison conditions. The way records are filtered in a query can impact both the way a query executes and performance. Indexing has a significant effect on how well the WHERE clause filtering performs.

The GROUP BY clause is used to aggregate records into summarized groups of records retrieved from a database. Groupings are best achieved as a direct mapping onto one-to-many relationships between tables.

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.  Try to avoid the HAVING Clause. In Select statements HAVING clause is used to filter the rows after all the rows are selected and is used like a filter.

Use specific column names in the SELECT clause instead of using ‘*’.  This will help with optimizing the performance of the query.

The ORDER BY clause sorts the record set according to the provided columns.

The TOP clause picks up the TOP n records.  TOP will execute after the ORDER BY clause and return top n sorted records.  If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

Jim Gilbert

ABOUT Jim Gilbert

Jim Gilbert is the Content Marketing Manager for AAJ Technologies. Jim is a 20 year digital marketing veteran, lecturer, author and former adjunct professor. He is also a 3 term past President of the Florida Direct Marketing Association. When not at AAJ, you can find him spending time with his family, biking around South Florida or enjoying some live music. You can reach Jim at: jim.gilbert@aajtech.com

AAJ Technologies

USA
Global Headquarters

6301 NW 5th Way Ste. 1700
Ft. Lauderdale, FL 33309
TEL: 954.689.3984

ARGENTINA
LATAM Headquarters

AAJ Technologies S.R.L.
Alvear 1670 – 4to Piso
(2000) Rosario, Santa Fe

Copyright © 2017 AAJ Technologies. All rights reserved.

See what successful digital
transformation looks like.

Read the case study ›

You have Successfully Subscribed!