Bindings

A script is more than a list of statements. LET names a value, every statement after it can use that name, and RETURN decides what the script answers with.

LET $city = 'Paris';
SELECT * FROM people WHERE city = $city;

Script is exact here, and it is the one thing to get right before any of this runs for you. A binding lives for the rest of the submission it arrives in. A client sends a whole script in one message — every statement, in order — so LET reaches the statements below it, which is the case every example on this page is written for. The command line submits one statement at a time, so at the prompt those two lines are two scripts and the second has never heard of $city. Put the group on one line, or between BEGIN; and COMMIT;, and it behaves as it does from a client.

A name is substituted, not looked up

This is the whole design, and everything else on this page follows from it.

When LET produces a value, that value is written into the statements that have not run yet — the same walk a parameter you supply from a client takes. By the time a later statement runs, the name is gone and a literal stands in its place.

The alternative would be a lookup table the evaluator consults while running. It reads the same and costs you the index: a planner asked to serve WHERE city = <look this up later> cannot see a value on the right-hand side, so it takes the scan. Substitution keeps the read on the path it would have taken if you had typed the value yourself.

LET $city = 'Paris';
SELECT * FROM people WHERE city = $city USING INDEX by_city;

That USING holds. The read is served from the index, because by the time it runs the statement is WHERE city = 'Paris'.

It reaches forward only

SELECT * FROM people WHERE city = $city;
LET $city = 'Paris';

Refused. $city is unbound where it is written, and the binding below it is not in scope for a statement that already ran.

That is not a limitation to work around — it is what makes a script readable top to bottom. A name means what the line above it said, and never something a line further down will decide.

A binding holds a value, not a query

LET $count = SELECT count(*) AS n FROM people;

The subquery runs once, where it is written, and $count holds what it answered. It is not re-run for each statement that names it. If you want the question asked again, ask it again.

RETURN decides the answer

A script answers with one outcome per statement. RETURN says which value the script as a whole is about.

LET $found = SELECT * FROM people WHERE city = 'Paris';
RETURN $found;

It takes an expression, so it can answer with something assembled rather than something read:

LET $open = SELECT count(*) AS n FROM orders WHERE status = 'open';
LET $shut = SELECT count(*) AS n FROM orders WHERE status = 'closed';
RETURN { open: $open, closed: $shut };

Bindings and supplied parameters are the same thing

A $name your client binds and a $name a LET produces are indistinguishable to every statement below. Both are substituted before the statement runs, and neither can become syntax — a supplied string containing ; DROP DATABASE prod is a string, in the position a string was written, no matter what it holds.

That is the property worth stating plainly: a parameter formatted into script text destroys it invisibly, because the resulting script still looks correct. Bind the value; never build the text.

See Parameters for the client side of the same mechanism.