FREE LESSON · Databases & data systems · 2 OF 4
How a SQL Query Optimizer Works
A query is an algorithm choice — Plans, cardinality, joins, and execution
SQL states a result; the optimizer chooses a procedure.
The planner rewrites expressions, estimates row counts and selectivity from statistics, and compares access paths and join orders with a cost model. Execution operators form a tree or pipeline: scans produce rows, filters discard them, joins combine them, and aggregates reduce them. A wrong cardinality estimate can make a disastrous plan look cheap.
Declarative syntax moves algorithm selection into a system that needs accurate evidence about the data.
Nested loop versus hash join
An indexed nested loop can be excellent when the outer side is small and each inner lookup is selective. A hash join can scan both inputs and match through an in-memory hash table, often better for large equality joins. If the build side spills, memory and I/O change the tradeoff.
There is no universally best join—only a best-enough plan under estimated data and resource conditions.