The beanquery SELECT query loosely follows standard SQL syntax, with some deviations that will be discussed throughout this manual. It should be noted that originally beanquery was designed to extract information from the postings table only. In this situation the FROM part of the SQL query was "hijacked" for transaction-level filtering and WHERE was used for posting-level filtering, which introduced a so-called two-level filtering syntax: The SELECT query structure looked like this (written in the informal EBNF language):

SELECT [DISTINCT] [<targets>|*]
[FROM <entry-filter-logical-expression> [OPEN ON <date>] [CLOSE [ON <date>]] [CLEAR]]
[WHERE <posting-filter-logical-expression>]
[GROUP BY <groups> [HAVING <aggregate-filter-expression>]]
[ORDER BY <groups> [ASC|DESC]]
[PIVOT BY <column1>, <column2>]
[LIMIT num]

Let us call it the traditional SELECT query form.

Note about the informal EBNF language

In this manual, queries are described using a simplified notation that shows the structure of a command. This notation is based on an informal version of Extended Backus–Naur Form (EBNF), but you do not need to know anything about EBNF to use it. It is designed to be easy to read and understand. Here is how to interpret it:

Words written in UPPERCASE are keywords that must be typed exactly as shown
Items in square brackets [ ... ] are optional
The symbol | means “or” (choose one of the alternatives)
Text inside angle brackets <...> is a placeholder — you replace it with your own value
Items written next to each other should be written in that order

Example:

SELECT [DISTINCT] <targets | * > [FROM <expression>]

This means:

* Start with `SELECT`

* Optionally add `DISTINCT`

* Either provide one or more targets (what you want to select) or put asterisks (*)

* Then optionally put `FROM`, followed by some expression (expressions are discussed later)

In simple terms: Think of this syntax as a template for writing queries: It shows what parts are required, what parts are optional, and where you need to insert your own values.

Later, in Beancount v3, when beanquery was moved to a standalone tool, it was extended to query more tables (see above). In this situation the FROM part was needed again to select a table, so a new form of the SELECT query was introduced:

SELECT [DISTINCT] [<targets>|*]
[FROM (#<table-name> | "<table-name>" | <table-name>) | ( <select-query> )]
[WHERE <posting-filter-logical-expression>]
[GROUP BY <groups> [HAVING <aggregate-filter-expression>]]
[ORDER BY <groups> [ASC|DESC]]
[PIVOT BY <column1>, <column2>]
[LIMIT num]

In this form the FROM clause can include either a table name of a subquery. The subqueries are discussed in the section section 14. The table name can be presented in 3 different forms:

with the # symbol in front (E.g. #transactions)
inside the double quotes (E.g. "transactions")
just by a table name (E.g. transactions)

Let us call this form the #table query form, even though not only #tables are supported (historically #table form was the 1st ones to be introduced, hence the name). Note that:

The #table form is activated either by naming a table in the FROM clause — as #<table-name>, "<table-name>", or <table-name> — or by putting a subquery ( <select-query> ) there (see section 14)
The #table form allows querying tables other than the postings table as well as querying of sub-queries (which act as a table), but when used to query the postings table (which is possible), it lacks some functionality available in the traditional form, namely the [OPEN ON <date>] [CLOSE [ON <date>]] [CLEAR] part. (This may actually be a bql_iss_274, rather than a feature.)

So, to summarize:

In the traditional BQL, the FROM clause is used to describe the posting-level filter, not to identify the data source
In the #table form, the FROM clause identifies the data source — either a table (named with #, double quotes, or bare) or a subquery

In addition to a table reference, the FROM clause can also contain a subquery — a parenthesised SELECT whose result is used as the data source (a derived table). Subqueries may also appear inside WHERE expressions, together with the IN, ANY and ALL operators. Subqueries are covered separately in section 14. Currently beanquery supports both query types. Let us explore this with a simple ledger.