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.
How We Script SQL:
SELECT FROM WHERE GROUP BY HAVING ORDER BY
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.