[ home ]

SQLite select in a nutshell

Incomplete, of course, but helps to refresh my mind.

simple select

    WITH ...
        
    [select-core]
        
        SELECT [ DISTINCT / ALL ] ...
        
        FROM ...                       : determine input-data for select
        
        WHERE ...                      : filter input-data
        
        GROUP BY ... [ HAVING ... ]    : compute result-rows (aggregation + calculation)
        
    ORDER ...
        
    LIMIT ...

2 types of simple select-statements:

join-operators

    [ NATURAL ]   [ ( LEFT [ OUTER ] ) | INNER | CROSS ] JOIN

generation of result-rows in simple select:

compound select

    WITH ...
        
    [select-core] ( [connect-statement] [select-core] ) *
        
    ORDER ...
        
    LIMIT ...

bonus-clue: CTEs

Non-recursive common table expressions (CTE) using WITH-clause act as temporary views during a single SQL-statement, improving ease of maintenance.