Does the ORDER BY clause return a Virtual Table?-CodePudding

My understanding is that relational tables aren't ordered.

I also understand that each step, or phase, of the query execution returns a "virtual table" which is passed as input to the next phase.

But if tables are never actually ordered, what's happening during/after the ORDER BY phase?

I'm just trying to understand what might happen with a query like this:

SELECT col1, col2 
FROM mytable 
ORDER BY col1 
LIMIT 1;

Edit:

To clarify. I know what the query above outputs. I'm trying to better understand each phase/step of the underlying execution.

The (logical) order of execution for the above query would be:

FROM 
SELECT
ORDER BY 
LIMIT

I'm trying to understand what's going on during the ORDER BY phase. My understanding is that a virtual table is passed from the SELECT phase to the ORDER BY phase (in this case, a table with col1 and col2, but I don't know what's being returned by ORDER BY and subsequently passed to LIMIT.

CodePudding user response：

The underlying table is not sorted when you use ORDER BY, only the results returned by the SELECT statement are. That query will return the first result from mytable. Since the default order is ASCENDING, it will be the lowest value in col1.

CodePudding user response：

the order is in tables unknown, and by definition unsorted

a result set as the end product of a SELECT without an ORDER BYis also unsorted.

but as the ORDER BY is the penultimate command before LIMIT and OFFSET , the result set is in that specific order

CodePudding user response：

Does the ORDER BY clause return a Virtual Table?

Sometimes.

The database engine tries as much as it can not to produce a materialized table (that you call virtual table). Most of the time it's more efficient to work the rows one by one, so they can be successively processed by each execution step until it's returned to the client app.

However, this is not always possible. In such cases, the engine is forced to materialize an intermediate result that actually takes the form you are thinking about. But again, this is expensive, and is usually avoided.

The (logical) order of execution for the above query would be: FROM SELECT ORDER BY LIMIT

No. This is just how a SQL query is written and is unrelated to the actual execution steps. Take that sequence as a good pedagogical tool, useful [for learning purposes only] to understand how the result is produced. Behind the scenes, the engine cheats in all kinds of ways to do as less effort as possible to produce the result you asked.