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 list

    The tables to join to the LOOKUP table, each a quoted name such as "assets". The LOOKUP table is not repeated here

    Required: Yes
  • ON([…])

    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 "assets.id"

    Required: Yes

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
ErrorWhat happenedHow to recover
HTQL-1002The 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 nameWrite the columns as pairs: ON(["a.id", "b.id"])
HTQL-5001 JOIN requires exactly 2 argumentsJOIN has no ON, or has more than two partsWrite JOIN([tables], ON([pairs]))
HTQL-1002 references no joined tableA pair of columns does not name any table in the listName each column with the joined table, such as "assets.id"
column reference "id" is ambiguousA column both tables have is written without its tableWrite 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.column in 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.