Order by Has Come a Long Way

Back to Devoured - May 15, 2026

DEVOURED

Order by Has Come a Long Way

DevOps databasesql Modern SQL

SQL's `ORDER BY` clause has significantly evolved to support non-selected columns and expressions, making it more flexible, and users should prioritize execution plans over simplistic "filter early" advice.

What: The `ORDER BY` clause in SQL has gone from only allowing selected columns and no expressions in the 1980s to supporting non-selected columns and arbitrary expressions since SQL:1999. SQL:2003 added explicit `NULLS FIRST|LAST` ordering, and SQL:2008 enabled `ORDER BY` within subqueries for clauses like `FETCH FIRST`. The article emphasizes that SQL has three 'orders' (syntax, logical, actual execution), and developers should trust actual execution plans.

Why it matters: This article provides valuable historical context and clarifies common misconceptions about SQL query optimization, revealing that modern SQL engines handle complex optimizations automatically and that adherence to basic syntax order for performance is often unnecessary.

Takeaway: Trust your database's execution plan for performance optimization rather than blindly applying generic advice like "filter early" using subqueries, as the engine likely optimizes this automatically.

Deep dive

ORDER BY was initially highly restricted in SQL, only allowing references to selected columns, no expressions, no explicit null ordering, and only as the last clause.
SQL:1999 significantly expanded ORDER BY to allow non-selected columns and arbitrary expressions. To achieve this, the standard specifies a "syntactic transformation" where the database engine internally adds the non-selected column to the SELECT list and then removes it from the final result.
SQL:2003 introduced explicit NULLS FIRST | LAST ordering, as the default placement of null values was previously implementation-defined.
SQL:2008 allowed ORDER BY within subqueries, primarily to support clauses like FETCH FIRST (similar to LIMIT or TOP) for truncating results.
The article highlights three distinct "orders" in SQL: syntactic (how clauses are written), logical (how the standard defines evaluation), and actual execution (what the database engine truly does).
It strongly advises against applying generalized performance mantras like "filter early" to syntactical or logical order, stressing that database engines have optimized push-down filtering for decades.
Developers should examine the actual execution plan generated by their database to understand and optimize query performance, rather than relying on oversimplified infographics.

Original article

Full article content is not available for inline reading.

Read the original article →

Explore all 57 articles in today's digest