Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content