There has been a fair bit of high level stuff about how to use X, but no low level stuff about why it is there and why it is needed.
The SQL language is based around doing things to tables. In order to manipulate a table it has to have a name. This is where the X comes in. The full SQL syntax would be SELECT * FROM (...) AS X which makes it a bit more obvious that X is being used as a table name. Or to be more precise it is being used as an alias for a table name.
If you look at SELECT A,B,C FROM MYDB.DBO.MYTABLE AS X it is easy to see that X is used here as a table alias.
Now that you gave the alias, it can be put to use. In both examples the alias is useful in a WHERE clause, such as WHERE X.A = 5 or in a join WHERE X.A = Y.B
Giving a table an alias can help with making WHERE clauses easier to read. If you have a derived table as in the OP, then the derived table does not have any name of its own so a table alias becomes mandatory. This is the case regardless of any WHERE clauses.
There are some aspects of the SQL language that seem overly strict. IMHO requiring a table alias for a derived table when no subsequent WHERE clauses exist is an example of being overly strict. But we can only work with the tools we have.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara