It was a few weeks ago during an interview where I was asked to explain how to display the total number of comments a post had with SQL. My mind both went blank during it and realized after that I had forgotten a few SQL clauses from having used Active Record so much and not using these clauses in my projects. During a meet up I had met a Data Scientist who had noted that they had not learned the SQL execution order as I did and forgotten about many weeks ago. After getting home I had decided to go and look up what the SQL execution order is, refresh my memory about it and what clauses exist. This blog will be about just that.
These are the 7 steps/clauses of execution on SQL that I remember using in various labs and projects so far.
1. FROM / JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT / OFFSET
FROM / JOIN
The first to be executed is the FROM clause followed by the JOIN clause. These two determine what set of data will be used in the query along with how data will be merged. This can cause temporary tables to be created that hold the data under the hood.
WHERE
The second to be executed after the FROM / JOIN clause. This limits the data by values in the table's columns and can be by any data type.
GROUP BY
Executed after WHERE but acts sort of like WHERE. This clauses groups values in a column by common values and can be used with sum or count.
HAVING
After the GROUP BY executes HAVING follows. This clauses further filters the values in a true or false fashion.
SELECT
The last in the filtering of data this clause follows HAVING. This clause selects what columns you want data of.
ORDER BY
Executing after SELECT, this clause orders the data in ascending or descending fashion by column or columns selected.
LIMIT / OFFSET
The last to be executed after all the clauses. This clause simply limits how many rows of data will be returned. Best for avoiding returning large amounts of data and speeding up queries.
Hopefully, after this light list of SQL clauses and their order of execution, this information can be referenced and made easier to study. Getting over being nervous for interviews and not going blank during them is another challenge. This can only be done by doing more interviews and getting into a rhythm, also by applying these clauses in personal projects to see how to best use them or muscle memory. Thanks for reading through and I hope it helps.
