Relix

Problem solving

Which rows, and which columns

Grain: one row per input row · Class: Selection · Signals: which, that match, filter, only the ones · Operators: σ, π, ⋈, ⋉

The problem

"Which paid orders are over £100, and can I see just the order number and the amount with a surcharge added? And which orders come from a customer in the north?"

How to recognise it

The question says which, that match, filter, or only the ones that …. It asks for a subset of rows you already have, or a subset of their columns, and — this is the tell — the grain does not change. One order in, at most one order out. If the answer would have fewer things than one per input row it is a summary (γ); if it could have more, a join is fanning out and that is the mistake this recipe exists to avoid.

Two operators do almost all of it:

A third question — which rows are also in another table? — is still selection, not a summary, so it must keep each row once. That is a semi-join (⋉), not a plain join.

The data

Customers := [
| customer | region |
|----------|--------|
| Ann      | North  |
| Bo       | South  |
| Cy       | North  |
];

Orders := [
| order_id | customer | amount | status |
|----------|----------|--------|--------|
| 1        | Ann      | 120    | paid   |
| 2        | Ann      | 40     | paid   |
| 3        | Bo       | 200    | open   |
| 4        | Cy       | 90     | paid   |
| 5        | Dee      | 60     | paid   |
];

Dee has an order but is not a customer; Bo's big order is not paid.

Recipe 1: keep the rows, then the columns

σ takes a condition — combine tests with ∧ and ∨. Keep the paid orders over £100:

Query
query { σ amount > 100 ∧ status = "paid" (Orders) };
Result
 order_id  customer  amount  status
 ────────  ────────  ──────  ──────
        1  Ann          120  paid
(1 row)

π chooses and derives columns; expr → name names a derived one. Name a view after what its rows are, and build on it:

Query
HighValue := { σ amount > 100 (Orders) };
query { π order_id, customer, amount (HighValue) };
query { π order_id, amount, amount * 1.1 → with_surcharge (HighValue) };
Result
 order_id  customer  amount
 ────────  ────────  ──────
        1  Ann          120
        3  Bo           200
(2 rows)
 order_id  amount  with_surcharge
 ────────  ──────  ──────────────
        1     120             132
        3     200             220
(2 rows)

Recipe 2: keep the rows that are also in another table

Which orders come from a northern customer? This is a filter on Orders — one row per order — so reach for a semi-join, which keeps a left row when a match exists and never duplicates it. A semi-join takes a condition, like every join but the natural one:

Query
North := { σ region = "North" (Customers) };
query { Orders ⋉ Orders.customer = North.customer North };
Result
 order_id  customer  amount  status
 ────────  ────────  ──────  ──────
        1  Ann          120  paid
        2  Ann           40  paid
        4  Cy            90  paid
(3 rows)

A plain join answers a different question — it brings the customer's columns along, so it is the right tool when you want them:

Query
query { π order_id, customer, region (Orders ⋈ North) };
Result
 order_id  customer  region
 ────────  ────────  ──────
        1  Ann       North
        2  Ann       North
        4  Cy        North
(3 rows)

Variations

Pitfalls

Query
  Contacts := [
  | customer | channel |
  |----------|---------|
  | Ann      | email   |
  | Ann      | phone   |
  | Cy       | email   |
  ];
  query { π order_id, customer, amount (Orders ⋈ Contacts) };
  query { Orders ⋉ Orders.customer = Contacts.customer Contacts };
Result
 order_id  customer  amount
 ────────  ────────  ──────
        1  Ann          120
        1  Ann          120
        2  Ann           40
        2  Ann           40
        4  Cy            90
(5 rows)
 order_id  customer  amount  status
 ────────  ────────  ──────  ──────
        1  Ann          120  paid
        2  Ann           40  paid
        4  Cy            90  paid
(3 rows)

Check it