The SELECT Pipeline
The SELECT syntax
tries to represent a generic framework that is capable of expressing
many different types of queries. To achieve this, SELECT has a large number of optional clauses, each with
its own set of options and formats.
The most general format of a standalone SQLite SELECT statement looks like this:
SELECT [DISTINCT]select_headingFROMsource_tablesWHEREfilter_expressionGROUP BYgrouping_expressionsHAVINGfilter_expressionORDER BYordering_expressionsLIMITcountOFFSETcount
Every SELECT command
must have a select heading, which defines the returned values. Each
additional line (FROM, WHERE, GROUP
BY, etc.) represents an optional clause.
Each clause represents a step in the SELECT pipeline. Conceptually, the result of
a SELECT statement is calculated by
generating a working table, and then passing that table through the
pipeline. Each step takes the working table as input, performs a specific
operation or manipulation, and passes the modified table on to the next
step. Manipulations operate the whole working table, similar to vector or
matrix operations.
Practically, the database engine takes a few shortcuts and makes plenty of optimizations when processing a query, but the end result should always match what you would get from independently going through each step, one at a time.
The clauses in a SELECT statement are not evaluated in the same order they
are written. Rather, their evaluation order looks something like
this:
FROMsource_tablesDesignates ...