Search code examples
sqldatabasedatabase-agnostic

Is a sort passed through in a sub-select

Is the following deterministic behavior in the SQL standard?

SELECT *
FROM (
    SELECT *
    FROM myTable
    ORDER BY name ASC
)

Or are 'order-by's basically thrown away when within a sub-select? Of course the 'proper' way to do it would be to put the ORDER BY in the outer select. However, in my use-case I am passed an already-created SQL statement by the end user and I have to wrap that in a sub-select that adds some additional behavior.

Solution

  • The ordering which may (or may not) happen in the inner query will not stick in the outer SELECT *. The SQL optimizer might be smart enough to realize that the inner ORDER BY will not affect the result set, and remove that sort. Otherwise, you will pay the penalty of a sort without any benefit.

    The only way to get the ordering you want in the final result set would be to add an ORDER BY clause the outer query. If you can't do that, then you need to rethink your approach.

    Not an original reference to the ANSI-92 standard, but this discussion on the MariaDB site mentions that ORDER BY in a subquery is not allowed by ANSI SQL: https://mariadb.com/kb/en/why-is-order-by-in-a-from-subquery-ignored/

    However, the discussion goes on to state the MariaDB allows the syntax, because ORDER BY with LIMIT changes the result set, due to the ordering.