Suppose we have the following query:

SELECT DISTINCT COLUMNA, AGGREGATE_FUNCTION(COLUMN OR EXPRESSION), COLUMNB
FROM TABLEA
JOIN TABLEB ON TABLEA.COLUMN = TABLEB.COLUMN
WHERE [Constraints for Query]
GROUP BY COLUMN
HAVING [Constraints for query]
ORDER BY COLUMN [ASC/DESC]
LIMIT [NUMBER]
OFFSET [NUMBER];

The order of execution would be as follows:

  1. FROM AND JOIN - These would be executed first in order to gather the data set that is being requested. Subqueries would be included here. It should be noted that behind the scenes, temporary tables can be created which would contain the every column and row of the tables that are going to be joined.

  2. WHERE - Once the data set has been retrieved, the where clause is then implemented on the rows of the data set. Any rows that do not fit the criteria in the where clause are then disposed of. Only the columns in the tables selected in the query (i.e. TABLEA, TABLEB) can be accessed. It should be noted that aliases used in the query are usually not accessible, depending on the database system being used (e.g. MYSQL, MS SQL) since they rely on certain areas of the query that have not been executed yet.

  3. GROUP BY - After the constraints of WHERE is applied, the data set is then grouped by values that are common in the column next to the GROUP BY keyword. The rows that are returned are only the ones that have values that are unique in the column. Use this when there are aggregate functions in the query.

  4. HAVING - If you have GROUP BY, HAVING is applied to the data that has been grouped and the rows that do not match are then disposed of. Aliases are not accessible here like in other database systems.

  5. SELECT - If there expressions as part of SELECT, they are then processed.

  6. DISTINCT - Any rows remaining will be checked if there are values that are duplicate in the column with the keyword DISTINCT next to it in the query. If there are, it will be discarded.

  7. ORDER BY - Rows will order the data selected in either ascending, which is the default, or descending order. Aliases are accepted here since the portion of the query which contains the SELECT keyword has already been processed.

  8. LIMIT OR OFFSET - Rows that do not meet the condition set in either LIMIT or OFFSET are then disposed of, returning the query results. LIMIT allows setting the number of rows to return. OFFSET allows the skipping of a certain number of rows before returning the query.

A basic SQL query does not need to contain all of what was explained above. It does however, give the person that is writing this query the capability of getting the data in the way they want without having to write code.