JOIN
Read a record together with the matching record from another table, in one statement and one result.
Syntax
JOIN is a clause of LOOKUP. Name the tables to join, then the pairs of columns that match.
LOOKUP(
table,
JOIN(
["other_table", …],
ON(["other_table.column", "table.column", …])
),
SHOW("table.column", "other_table.column")
)Parameters and arguments
JOIN takes exactly two arguments: the list of tables, then ON(…).
tables listThe tables to join to the LOOKUP table, each a quoted name such as
Required: Yes"assets". The LOOKUP table is not repeated hereON([…])Pairs of columns that must be equal, written as one flat list: the first two names are one pair, the next two are the next pair. Name each column with its table, such as
Required: Yes"assets.id"
Examples
Entries that have an asset with the same identifier, with the asset's title.
Return type and behaviour
JOIN returns the rows of the LOOKUP table, each with the columns of the matching row from the joined table. It is a left join: a row with no match is kept, and the joined columns are null.
Columns come back under their own names, without the table prefix. If both tables have a column with the same name, rename one with AS, as in SHOW("entries.id AS entryId", "assets.id AS asset_id").
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-1002 | The ON list has an odd number of names, so one has no partner. A single text such as "a.id = b.id" is read as one name | Write the columns as pairs: ON(["a.id", "b.id"]) |
HTQL-5001 JOIN requires exactly 2 arguments | JOIN has no ON, or has more than two parts | Write JOIN([tables], ON([pairs])) |
HTQL-1002 references no joined table | A pair of columns does not name any table in the list | Name each column with the joined table, such as "assets.id" |
column reference "id" is ambiguous | A column both tables have is written without its table | Write it as table.column |
Placement and context
Inside LOOKUP, among its clauses. Name the joined table's columns in SHOW, FILTER, SORT and DISTINCT as table.column, such as assets.title.
You can join several tables in one JOIN: each pair of columns joins the table it names. A table with more than one pair needs all of its pairs to match.
JOIN works beside NEST. JOIN adds a joined row's columns to each row, and NEST adds a list of children, so one LOOKUP can do both.
JOIN also names the source of a FOREACH: FOR x IN JOIN([orders, customers], ON([orders.customerId, customers.id])) BEGIN … END walks the joined rows.
Limitations
- Not supported: a single text in ON, such as
"assets.id = entries.id". JOIN's ON takes pairs of names. (NEST's ON accepts that form.) - Not supported: an inner or right join. JOIN is always a left join. To keep only the rows that matched, add a FILTER on a joined column, such as
FILTER(assets.title IS NOT NULL). - Write a column of the joined table as
table.columnin FILTER too. A column name that both tables have, written without its table, is refused as ambiguous. - Every table in the list needs at least one pair that names it.
Permissions and security
JOIN needs read access to every table it names.
Best practices
- Name every column with its table in SHOW and ON.
- Rename columns that share a name with
AS. - Add a FILTER on a joined column when you only want rows that matched.
- Use NEST rather than JOIN when a parent has many children and you want them as a list, not repeated rows.