SQL Clauses and their Order of Operation

What is SQL?

“SQL” stands for Structured Query Language and is used to manage, interact with, and retrieve data from a database. PDQ Inventory uses SQLite which is a lighter version of SQL’s syntax but still fully functional for extracting data. SQLite is file-based unlike other databases you may be more familiar with (Oracle, MySQL, PostgreSQL, Microsoft SQL Server), which are server-based.

If you’re familiar with SQL, you’re aware of the standard order of operation for its syntax. However, are you aware that the queries are not actually executed in the order we script them in? In most situations, there is a logical process for the way an SQL query is executed versus the way it is written. It is a slightly different order, but it is important to understand the ordering difference of the clauses to understand more about how the results are rendered.

Standard SQL syntax order begins with the ‘SELECT’ clause. However, the engine does not understand what it’s selecting unless it’s told where to find it first. Therefore, the logical order of SQL syntax begins with ‘FROM’ and filters data through joins and the other clauses following until it is told what to SELECT more toward the end.

 ‘Standard Order’

How We Script SQL:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

SQL clauses

‘Logical Order’

How SQL Executes:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Once you dive more into it, there are additional SQL clauses that come into play but for now, these are the 6 main SQL clauses and their order of operation. For standard and best practices, we will script our syntax in standard order with SQLite in PDQ Inventory.

 

Your email address will not be published.

Your Name

This site uses Akismet to reduce spam. Learn how your comment data is processed.