> In SQL it boils down to the fundamental syntactical requirement to put the SELECT clause before the FROM clause
No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls.
The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary.
If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them).
As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.
I would not throw someone at CTEs who is using SQL Server - just use temp tables for each component you would be CTE-ing, CTE's dont get any benefit from re-use except from a code perspective, whereas composing your sets into temp tables will often get you exactly what you want, individual sets that you can re-use throughout your code.
Very good point indeed. I'll add to that, that you can trivially examine temporary tables wheres CTEs don't have that transparency. Thanks!
Edit: I'm going to clarify this. It is a mistake to throw CTEs at a beginner but to be clear, CTEs have very important advantages over the using temp tables to hold intermediate results. It comes down to efficiency.
MSSQL only optimises within a single statement; it does not optimise across statements, so if you have a several queries comprising a single CTE, the optimiser has plenty to get its teeth into and may produce a much more efficient query plan.
Also a query plan of a complex CTE can (depending on what you're doing) end up being a straightforward pipeline where one result feeds into another into another and finally gets spat out at the end. That can be very efficient. If you use temp tables you spool intermediate results (which may be large), re-read, spool into another temp table etc. If you're working with a large data set that can use up a lot of memory, and if it's large enough that it has to spill to disk... oh dear.
So for beginners, yes, as you get more expertise, CTEs are the way to go (depending, of course, on various factors)
(Conversely, temp tables do have a definite cardinality, whereas queries in a CTE are estimates and can be badly out leading to very poor query plans. So temp tables can work to your advantage here).
No offence but if you are at the level of struggling with the syntax then you should not be using SQL until you have more experience. There have been plenty of well-founded critiques of the crappy syntax of SQL, but it's not ultimately that hard. There are worse things you'll have to cope with such as the implications of 3-valued logic that comes with nulls.
The giving-tables-alias-names feature has been standard probably since the very first standard was released. In some cases such as self-joins, it is necessary.
If I can give you a piece of advice, arrange things using the 'with' clause and learn to break things down as simple as possible at each step. Let the optimiser sort things out for you. If performance is poor, look at the query plan. Also understand you don't have to write everything in one huge statement. Spool intermediate results off to temp tables if that helps (not @tables but #tables - @tables have problems with them).
As for the XML, I guess that may be better handled outside the DB. IMO XML should never be made part of SQL. Good luck.