The Query Builder Layout¶
To open the Query Builder, go to the SQL Builder page. It has three main areas — the Object List, the Query Building Area, and the Columns pane. Once you build a query, its generated SQL is sent to the SQL page, where you can run it just like a script you typed yourself.
Object List¶
On the right of the page, the Object List shows the database's tables, virtual tables and views. Drag an object from here into the Query Building Area to add it to your query.
It lists what the tree lists, and the same two settings govern both: Show system tables and Show shadow tables, on the Tree panel page of the Options dialog. Both are off to begin with, so sqlite_master and the private tables an FTS or R-Tree table keeps for itself — _data, _idx, _content and the rest — stay out of a list you are picking join sources from. Turn either on and the objects appear here as well as in the tree, without reopening the page.
A shadow table can be queried like any other, and the SQL page will take one if you type its name. It is the picker that leaves them out: their contents are the module's own business, their layout is undocumented, and one FTS table contributes four or five of them.
Show "main." prefix¶
Beneath the list, Show "main." prefix decides whether an object in the open database is named with the database it lives in — main.customer rather than customer — in the list, on the boxes in the Query Building Area, and in the SQL the builder writes. It is off, because a name with nothing in front of it already means the database you have open: SQLite reads an unqualified name as the default database, main.
A database you have attached is always named, whichever way this is set. main. is the only prefix this leaves out, and only because it is what an unqualified name already says.
Turn it on and the prefix comes back everywhere at once, including in queries already on the canvas. The setting is remembered between sessions, and it changes nothing about what a query does.
Note
An unqualified name is read as main only as long as no temporary table has the same name: SQLite looks in temp first. Turn the prefix on if you work with temporary tables that shadow real ones and want the SQL to say which one it means.
Query Building Area¶
The Query Building Area is the main canvas, where your query takes shape visually. Here you add the source tables and derived tables, draw the joins (links) between them, and set the properties of each table and link.
Above the canvas, a set of tabs lets you switch between the main query and any sub-queries. In the top-right corner, a small panel marked Q is the union sub-query control, where you add and arrange union sub-queries.
Columns pane¶
Below the canvas, the Columns pane is where you work with the query's output columns and expressions. Here you define column aliases, choose sorting and grouping, add aggregate functions, and enter selection criteria.