CODEWITHSUNDEEP
sql-serversubquerysql-order-by
Pankaj
Pankaj

Reputation: 366

Why order by restricted in SQL subqueries?

On using order by in subqueries, an error is thrown: For example:

set rowcount 10
select * from XXX where Col1 in(
    select Col2 from YYY
    order by Col3 desc
)

Why is this type of queries restricted in SQL Server? The error thrown was:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Upvotes: 4

Views: 175

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

Why?

Short Answer:

Because sets have no order.

Longer Answer:

SQL is an implementation of relational calculus, which is based on tuple-sets (tables, rowsets, etc.). Sets do not an order (unlike the related concept, lists which are sets with ordering).

Further, there's (generally) no benefit to applying ordering to a set before the final output, and a considerable amount of harm, because:

  • The optimizer can add or remove ordering at any stage of the query execution, as it sees fit, to increase the performance of the query,
  • So, it would just be (considerable) extra work,
  • And the optimizer might just undo it the next step anyway
  • And queries and stages can be done in parallel, as the optimizer sees fits, and this too is generally in conflict with preserving an ordering.
  • So applying it earlier, doesn't mean that that order will still be there in a later stage

The only place it commonly makes sense is in the final output, because data has to be transmitted serially there anyway.

The only places it makes sense before the final output is for things like TOP (N) which needs an ordering to determine which rows are the "Top" rows.

It can also make sense for certain aggregate functions, which is why many of them have their own ORDER BY clauses. Also for constructing XML results in intermediate stages.

Upvotes: 6

Related Questions