Declarative vs Imperative Query Languages

Why saying what you want (SQL, Cypher, a MongoDB pipeline) instead of how to get it lets the database pick indexes, join orders and parallelism, and where MapReduce fits.

Overview

There are two ways to ask a computer for data. Imperative code spells out the steps: loop over these records, check this field, add to that list. A declarative query states the result you want (these columns, from rows that match this condition, grouped this way) and leaves the steps to the system. SQL is the best-known declarative language; graph pattern languages like Cypher and SPARQL, and even CSS selectors, work the same way.

The difference matters because whoever chooses the steps also owns their performance. With a declarative query, the database chooses, and it can choose better when data, indexes or hardware change, without anyone touching the application.

The same question, two ways

You write — The algorithm: loops, conditions, order of steps — The result: filter, join, group, sort
Who picks indexes and join order — You, in code — The query planner
When data grows or an index is added — You rewrite the code — The planner may pick a new plan
Parallel execution — You write it yourself — The engine can split the work
Escape hatch — Full control — Hints, rewriting the query, or code

Here is an imperative version of 'total spent by customer 42'. It always reads every order, because the loop says so.

The declarative version is one line of SQL: SELECT sum(total) FROM orders WHERE customer_id = 42. It says nothing about scanning or indexes, which is exactly what leaves the database free to choose.

What the planner does with a declarative query

A relational database turns a SQL statement into a plan: a tree of steps such as scans, joins, sorts and aggregates. PostgreSQL's planner considers a sequential scan and every index that matches the query, and for joins it weighs nested loop, merge join and hash join. It estimates the cost of each option from table statistics (collected by ANALYZE) and runs the cheapest. When a query joins many tables, PostgreSQL stops trying every order and switches to a genetic search, controlled by geqo_threshold.

You can watch the planner change its mind. The query below never changes; only the index does.

PostgreSQL 17.5 printed these plans when we ran the block (one plan line per row, indentation dropped).

Aggregate — Aggregate
-> Seq Scan on orders — -> Bitmap Heap Scan on orders
Filter: (customer_id = 42) — Recheck Cond: (customer_id = 42)
-> Bitmap Index Scan on orders_customer_idx
Index Cond: (customer_id = 42)

Before the index, the only option is to read all 100,000 rows. After it, the planner reads the 20 matching rows through the index. The final query returns 20 orders and 1524.0 spent.

The exact plan depends on the version, settings and data (a larger table on a server with parallel workers may show a Gather and a Parallel Seq Scan instead), which is the point: the database decides, and EXPLAIN shows you what it decided.

Why declarative wins over time

Evolvability. A new index, fresh statistics or a smarter planner in the next release can speed up a query that nobody edited.
Parallelism. PostgreSQL can split one query across several CPU cores (parallel query), and distributed SQL engines split it across machines. An imperative loop runs the way it was written.
Less code to get wrong. Joins, grouping and sorting are written once, in the engine, and tested there.

The cost is less control. When the planner misjudges, usually because statistics are stale or the data is skewed, you fix it by running ANALYZE, adding an index or rewriting the query, not by stating the steps directly.

Declarative outside SQL

The same idea shows up wherever a system can plan for you.

SQL — Filters, joins, groups — Scans, join order and method, parallelism
Cypher, SPARQL — A graph pattern to match — Where to start and how to walk the graph
CSS selectors — Which elements get which styles — How the browser finds matches and repaints
MongoDB aggregation pipeline — A list of stages — Reorders some stages and uses indexes

A MongoDB pipeline looks step by step, but the server optimizes it. It moves a $match filter ahead of $project, $addFields or $sort stages when the filter does not depend on computed fields, and a $match that ends up first can use an index.

MapReduce: a middle ground

In 2004, Google published a paper describing MapReduce. You write two functions: map turns each input record into key-value pairs, and reduce combines all values that share a key. The framework runs them on many machines, splits the input, moves data between them and retries failed tasks, so programmers without distributed-systems experience could use a large cluster.

MapReduce sits between the two styles. The overall shape (map, shuffle, reduce) is fixed and the framework plans the distribution, but the logic inside each function is ordinary code the framework cannot look into. It cannot push a filter into an index or reorder steps inside your function, so a job typically reads all of its input.

Later systems put a declarative layer back on top. Spark SQL (2015) offers SQL and a DataFrame API whose queries go through an optimizer called Catalyst. MongoDB deprecated its own map-reduce in version 5.0 and points users to the aggregation pipeline.

What you will be able to do

Explain the difference between declarative and imperative queries with a concrete example.
Describe what a query planner does and why statistics matter.
Read a simple EXPLAIN plan and say why it changed after adding an index.
Place MapReduce between the two styles and say what later systems changed.

Key concepts

Declarative query: A query that states the result it wants and leaves the steps to the system. SQL, Cypher and SPARQL are declarative.
Imperative code: Code that states each step: loops, conditions and order. It runs exactly as written, which also means nothing can reorder it for you.
Query planner: The database component that picks the steps for a query: which scans, which join method and order, whether to run in parallel. Also called the optimizer.
Execution plan: The tree of steps the planner chose. EXPLAIN shows it without running the query; EXPLAIN ANALYZE runs it and adds timings.
Table statistics: Sampled facts about a table, such as row counts and common values, that the planner uses to estimate costs. ANALYZE refreshes them.
MapReduce: A model for batch processing on a cluster: map emits key-value pairs, the framework groups them by key, and reduce combines each group.
Predicate pushdown: Applying a filter as early as possible, ideally through an index, so later steps see fewer rows. A declarative engine can do it; opaque code prevents it.

When to use it

Explaining why a database choice depends on its query planner
Arguing for pushing filters and joins into the database instead of application loops
Comparing SQL engines, MapReduce jobs and dataflow systems in an architecture discussion
Debugging a slow query by reading its plan instead of guessing

Interview questions

Why is SQL declarative, and why does that matter?
How does MapReduce differ from SQL?
A report query got slow after the table grew. How do you approach it?