Relational Authoring

Grid deliberately supports several familiar ways to work with structured data. They are different reading styles over shared values, not separate execution languages. Choose the form that makes the operation easiest to recognize.

The source carrier must not choose the spelling. A query should read the same whether Orders is a small record collection, a Frame, a model table, or a supported external relation.

Quick Choice

Your thought	Prefer
Keep, drop, or rename a few fields	PICK, OMIT, RENAME
Apply a short sequence of obvious value transformations	>> or `
Filter/sort an ordinary spreadsheet array using already-computed masks or keys	FILTER, SORT, SORTBY, TAKE
Read one scalar summary from a model table	concise table method chain
Express row predicates, computed columns, grouping, joins, sets, or windows	SELECT
Name intermediate relational stages	WITH/CTEs or named relation assignments
Insert, update, or delete durable records	explicit table compound mutation

These are preferences for shared models and generated code, not artificial restrictions on small expressions that are already clear.

Projection

Use PICK, OMIT, and RENAME when the operation is fundamentally about record keys:

PublicUsers = users OMIT password_hash, reset_token
Identity = users PICK id, display_name
Normalized = source RENAME "Customer Name" AS customer

This form works naturally for one object as well as a collection. It also supports dynamic and glob keys, which are broader than GRSQL-1 projection.

Use SELECT when projection is one clause in a larger row query, when columns are computed, or when aliases establish a result schema:

Invoices = SELECT id, quantity * unit_price AS total
  FROM Lines
  WHERE quantity > 0
  ORDER BY total DESC

Static exact Frame PICK id, amount and SELECT id, amount FROM Frame lower to the same logical projection. The formatter should preserve the author's chosen surface rather than translating one into the other.

Filtering, Ordering, And Limits

Spreadsheet functions are clearest when their operands are already arrays:

Positive = FILTER(values, values > 0)
Top = TAKE(SORT(Positive, -1), 10)

Pipes are clearest for a short left-to-right transformation:

Top = (values
  >> FILTER(value => value > 0)
  >> SORT(_, -1)
  >> TAKE(10))

The lambda is an element predicate. In a pipe this lowers to the ordinary spreadsheet mask form FILTER(values, MAP(values, predicate)); outside a pipe, write FILTER(values, include_mask [, if_empty]) directly. This value pipeline does not become a relational WHERE merely because the source happens to hold records. Treat it as equivalent to WHERE only when the carrier is a row collection, the lambda is a row predicate, and FILTER's empty fallback is not observable.

Use SELECT when predicates and order keys are fields in a row schema, or when clause order matters to understanding the result:

Top = SELECT id, score
  FROM Candidates
  WHERE eligible = TRUE
  ORDER BY score DESC
  LIMIT 10

LIMIT without ORDER BY does not mean “top”; it observes whichever order the input relation carries. Write the order whenever row choice matters.

Scalar Table Reads

A concise table method is idiomatic for one obvious aggregate:

late_count = Orders.where(status = "Late").count()
paid_total = Orders.where(amount >= 0).sum("amount")

Use SELECT once the result has multiple outputs, grouping, a computed aggregate argument, HAVING, or ordering:

ByCustomer = SELECT customer, COUNT(*) AS orders, SUM(quantity * price) AS revenue
  FROM Orders
  WHERE status = "Paid"
  GROUP BY customer
  HAVING revenue >= 1000
  ORDER BY revenue DESC

Do not stretch a method chain to imitate SQL clauses. Do not wrap a one-line scalar aggregate in a multi-clause query merely to make it look more formal.

Joins, Sets, Windows, And Subqueries

Use SELECT for relational composition whose established public vocabulary is SQL-shaped:

• schema-aware joins;
• grouped and global aggregates;
• UNION, INTERSECT, and EXCEPT;
• analytic windows;
• derived relations, CTEs, and supported correlated subqueries.

These forms carry cardinality, null, alias, and ordering rules that generic array helpers do not imply. A lookup function is still preferable when the thought really is one keyed lookup rather than a relation join.

Name a window when several projections share its partition, order, or frame. Use QUALIFY when the result should retain only rows selected by an analytic output:

LatestOrder = SELECT customer, id, ROW_NUMBER() OVER newest AS rank
  FROM Orders
  WINDOW newest AS (PARTITION BY customer ORDER BY created_at DESC)
  QUALIFY rank = 1

For total null-safe equality in a row predicate, use IS DISTINCT FROM (or IS NOT DISTINCT FROM) rather than constructing a pair of IS NULL checks.

Naming Intermediate Work

Use a CTE when an intermediate relation exists only to explain one query:

Report = WITH active AS (
    SELECT id, region, amount FROM Orders WHERE status = "Active"
  )
  SELECT region, SUM(amount) AS total
  FROM active
  GROUP BY region

Use a named assignment when the relation is independently meaningful, reused, published, or inspected elsewhere:

ActiveOrders = SELECT id, region, amount FROM Orders WHERE status = "Active"
RegionalReport = SELECT region, SUM(amount) AS total
  FROM ActiveOrders
  GROUP BY region

An untyped named assignment remains a computed Workbook value; a CTE remains local to its query. Use a table-typed assignment when the relation is part of the model's durable, continuously maintained state:

table PaidOrders = SELECT region, amount AS revenue
  FROM Orders
  WHERE status = "paid"

table RevenueByRegion = SELECT region, SUM(revenue) AS revenue
  FROM PaidOrders
  GROUP BY region

TotalRevenue = RevenueByRegion.sum("revenue")
RevenueAlert = TotalRevenue > 100

This is a Live Table declaration. Deploying the model records the declared table's ownership and reconciles it into the existing materialized-table catalog before dependent formulas are primed. A relevant source-row mutation then updates the target table, dependent cells and realtime snapshots through one existing table-change path; it does not recompile the model or invoke a second relational evaluator.

The first admitted shapes are deliberately narrow. A row-preserving Live Table may project and rename direct columns from one declared table and may carry one comparison between a source column and a scalar literal. Its durable source-row identity lets one edit add, retract, or update exactly one target row. A grouped Live Table admits either one declared table source or an inner join of two declared tables on one equality key; one projected grouping column; and one aliased COUNT(*), SUM(column), AVG(column), MIN(column), or MAX(column). Join keys, grouping columns, and aggregate inputs must be explicitly qualified in the joined form. ORDER BY, LIMIT, HAVING, multiple group columns, multiple aggregates, distinct aggregates, compound/join filters, outer or multi-key joins, and computed row projections are not yet admitted as compiler-owned Live Tables. Such queries retain their existing Workbook evaluation rather than silently changing semantics.

Saving or refreshing a materialized projection through the operational API remains available for host-managed lifecycle effects. Live Tables make that ownership part of ordinary model source.

Equivalent And Non-Equivalent Forms

Equivalent surface forms must lower to one logical algebra before physical planning. Optimizers may recognize more equivalences over time, but syntax does not grant a preferred engine.

Some visually similar forms are deliberately not interchangeable:

Form	Why it is distinct
PICK with glob/dynamic keys	output schema may depend on runtime keys; GRSQL-1 projection is statically named
FILTER(data, mask, fallback)	fallback is spreadsheet value behavior, not relational empty-result behavior
positional SORT(array, index)	selects a physical array column; relational ordering names a semantic field/expression
TAKE(data, n)	observes current collection order; ORDER BY ... LIMIT n establishes order first
lookup function	returns one lookup result under function-specific miss rules; a join returns a relation with join cardinality
editing a query result	changes that value only; it is never implicit writeback to source rows

Mixing Styles

One expression may combine forms when each boundary remains obvious:

Preview = (
  SELECT id, amount FROM Orders WHERE status = "Open"
) PICK id, amount

Prefer one dominant style per relational stage. If a pipe accumulates branching, grouping, aliases, or several row-schema operations, rewrite that stage as SELECT. If a query exists only to remove one key, use OMIT.

Formatting

• Put each major SELECT clause on its own line once the query has more than projection and source.
• Start an enclosing ( on the assignment line for a multiline query; continuation newlines are enabled inside (...), [...], and {...}.
• Indent continuation clauses two spaces from the assignment.
• Require explicit AS for computed, aggregate, and window outputs.
• Keep short method chains on one line; name a stage before chains become hard to scan.
• Wrap pipes after about two stages, with the pipe token leading each continuation line.
• Preserve authored syntax during formatting; canonicalize casing, spacing, operators, and indentation within that syntax.

Reserved And Rejected SQL Forms

Grid recognizes several familiar SQL spellings that it deliberately refuses to parse. Each refusal is a stable, named diagnostic rather than a generic syntax error: the spelling is understood, and the rejection is the contract. Treat these diagnostics as redirects to a Grid-native form, not as gaps to work around.

Reserved Read Clauses: GRID_RELATION_SQL_RESERVED

ASOF JOIN and LATERAL are recognized where they would appear in a SELECT and rejected:

R1C1 = SELECT * FROM Trades AS t ASOF JOIN Quotes AS q ON t.symbol = q.symbol

error[GRID_RELATION_SQL_RESERVED]: ASOF JOIN is reserved for a later Grid
relational profile and is not part of GRSQL-1

The diagnostic reserves spelling, not semantics. A later profile may admit these clauses once they have carrier-independent semantics and conformance coverage; until then, restructure.

For an ASOF-style nearest-match join, use a supported join with a named window and QUALIFY:

LatestQuote = SELECT t.id AS trade_id, q.price AS price, ROW_NUMBER() OVER newest AS rank
  FROM Trades AS t
  JOIN Quotes AS q ON t.symbol = q.symbol
  WHERE q.quoted_at <= t.executed_at
  WINDOW newest AS (PARTITION BY t.id ORDER BY q.quoted_at DESC)
  QUALIFY rank = 1

For LATERAL UNNEST-style expansion of a nested collection, flatten with array functions before querying:

Orders = [{ id: 1, items: ["a", "b"] }, { id: 2, items: ["c"] }]
AllItems = REDUCE([], Orders, (acc, order) => VSTACK(acc, order.items))

CROSS JOIN in the join position is part of GRSQL-1, not reserved. The other spellings on the profile's not-in-GRSQL-1 list (comma joins, NATURAL JOIN, USING, GROUPING SETS, and so on) are not recognized specially; they fail with ordinary parse or capability diagnostics rather than this named reservation.

SQL Write Statements: GRID_RELATION_SQL_WRITE

SQL DML, DDL, and transaction statement heads are recognized wherever an expression is expected and rejected:

• INSERT INTO, UPDATE <target> SET, DELETE FROM, MERGE INTO;
• CREATE, ALTER, DROP, or TRUNCATE followed by TABLE, VIEW, or INDEX;
• bare BEGIN, COMMIT, and ROLLBACK.

The diagnostic names the recognized statement and states the redirect:

R1C1 = UPDATE Orders SET status = "paid" WHERE id = 1

error[GRID_RELATION_SQL_WRITE]: UPDATE ... SET is not a Grid relational
expression; use an explicit table compound mutation (+=, ^=, *=, or -=) or a
host migration boundary

Each write statement has one Grid-native form. The four mutation operators are type-dispatched on a table-typed target; see the table-mutation section of assignments.md.

SQL thought	Grid-native form
INSERT INTO Orders ...	table Orders += { ... }
UPDATE Orders SET ...	table Orders *= { ... } (patch by id or filter)
DELETE FROM Orders ...	table Orders -= { ... } (by id or filter)
MERGE INTO Orders ...	table Orders ^= { ... } (upsert on row id)
CREATE TABLE Orders	table Orders = { schema: "orders" } declaration
CREATE VIEW ...	Live Table table Name = SELECT ... (see Naming Intermediate Work)
ALTER / DROP / TRUNCATE	host migration boundary; catalog lifecycle is not model source
BEGIN / COMMIT / ROLLBACK	none; a rule body already commits atomically

Declaration redirects:

table Orders = { schema: "orders" }

table PaidOrders = SELECT region, amount AS revenue
  FROM Orders
  WHERE status = "paid"

Mutation redirects are model source inside WHEN/EVERY/AT rule bodies, where the durable table write runs after the rule's atomic cell-state commit. Mark the table declaration default when rules in the same model mutate it:

default table Orders = { schema: "orders" }
input order_received = FALSE
input shipment_confirmed = FALSE
input price_corrected = FALSE
input order_cancelled = FALSE

WHEN order_received THEN
  table Orders += { name: "Acme", amount: 100 }
END

WHEN shipment_confirmed THEN
  table Orders ^= { id: "rec01HXYZ", status: "Shipped" }
END

WHEN price_corrected THEN
  table Orders *= { id: "rec01HXYZ", amount: 200 }
END

WHEN order_cancelled THEN
  table Orders -= { id: "rec01HXYZ" }
END

A standalone top-level table mutation is an explicit host command that commits once when submitted, rather than becoming a recalculating formula; see assignments.md.

The rejection matches the statement shape, not the bare word. For the multi-keyword heads, a write keyword alone remains an ordinary name:

UPDATE = 1
Total = UPDATE + 1

BEGIN, COMMIT, and ROLLBACK are rejected as bare keywords in expression position, so do not use them as value names in generated code.

See Also

• style-guide.md
• cookbook.md
• infix-operators.md
• reference.md
• features.md