If SQL is declarative, who decides how the join actually gets run?

An ordinary query asks for orders placed last month by customers in one country, with the customer name, sorted by date. The text fixes the tables, the conditions, the relationship, the columns returned and the output order. It does not say to use an index on the country column, or to start from customers, or to combine with orders by nested loops, or to sort at the end rather than walking an index that is already in date order. Those are method questions. The query leaves every one of them open, so the database has to settle them before it reads a row.

Read a query by asking what it did not say. For each table there is a choice between a scan and a seek into one of its indexes, and if several indexes apply there is a choice between them. For each pair of inputs there is a choice among join methods with different cost shapes. For the whole set of tables there is an order. For a sort or a group there is a place in the plan to do it, and an input already in the right order can make it free. The SQL names the result; the planner names the route.

Here $n$ is the number of tables, $m$ is the number of join methods, three in most systems, and $a$ is the number of access paths per table, a scan plus the usable indexes. Three tables with two access paths each gives $3! \cdot 3^{2} \cdot 2^{3} = 6 \cdot 9 \cdot 8 = 432$ plans. Six tables gives $6! \cdot 3^{5} \cdot 2^{6} = 720 \cdot 243 \cdot 64$. Work that out by hand: $720 \cdot 243 = 174{,}960$, then $174{,}960 \cdot 64 = 11{,}197{,}440$ plans. The factorial dominates. The planner cannot enumerate that in a few milliseconds, so it searches and prunes, which means the best plan is sometimes never considered.

The natural guess is that writing JOIN fixes the join. It does not. JOIN says the two inputs are related and how; it does not say nested loop, hash or merge. The planner compares plans by estimated cost, not by running them, so it chooses the cheapest estimate. That distinction matters: the planner can choose correctly over numbers that are badly wrong. The numbers come from statistics about the tables, and statistics are a summary, not the data.

Where this stops being true is not in the language but in the assumptions. If the statistics are fresh and the columns independent, the estimate can be close. If the data is skewed, if two conditions are correlated, if the value is supplied as a parameter, or if the statistics are stale, the estimate can be off by orders of magnitude. The planner also prunes: it does not consider every plan, so a good plan may be missed. And the fix depends on the cause. A missing index changes the access paths; stale statistics change the estimates; a query rewritten to expose a filter can change the join order. Adding an index to a query whose estimate is wrong can make the plan worse.

The declaration ends at the answer. The method is the planner's, and the plan is its guess at the cheapest way to produce that answer from the statistics it has. When the guess is wrong, the query is not a little slower: the same text can read hundreds of times more rows.