Why order is limited in SQL subqueries

Question

When using order in subqueries, an error occurs: for example:

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

I wanted to understand why this type of query is limited in SQL-Server? The error was:

The ORDER BY clause is not valid in views, built-in functions, views, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified.

Answer 1

Why?

Short answer:

Because the sets have no order.

Longer answer:

SQL is a Relational Calculus implementation based on tuple sets (tables, rows, etc.). Sets do not have any order (unlike the corresponding concept, Lists, which are sets with ordering).

, ( ) , :

• , , ,
• , () ,
• , , , , .
• , , , -

, , - , -.

, , TOP (N), , , "" .

Aggregate, ORDER BY. XML- .