Using SQLite

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_heading
    FROM source_tables
    WHERE filter_expression
    GROUP BY grouping_expressions
        HAVING filter_expression
    ORDER BY ordering_expressions
    LIMIT count
        OFFSET count

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:

1. FROM source_tables

   Designates ...