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

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:

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.

See Also

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