What does AS followed by some queries mean at SQL?

Question

I know AS is used to create an alias name in SQL. I know with is used if you want to save results of intermediate query or create a temp table. For example, something like the following:

with new_table as
(select * from order where order.id is Not NULL)

So the above query lets you reuse the new_table in another query. However what if you do not include with and write the following:

new_value as
(
  select
   A as Age,
   W as weight
  from
    order 
)

The as inside the select are creating the alias but what does the new_value as do?

it is different than with new_value as ?

Answer 1

For a single query, there is one WITH that can apply to multiple comma-separated CTEs. The CTEs are then followed by the main part of the query that can reference them.

For example,

WITH
 mycte1 AS
  (SELECT 1 as a),
 mycte2 AS
  (SELECT 1 as b),
 mycte3 AS
  (SELECT * FROM mycte1 INNER JOIN mycte2 ON a=b)
SELECT *
FROM mycte3

The CTE mycte1 isn't special just because it follows the WITH. The WITH is for all 3 CTEs (mycte1, mycte2, and mycte3).

Note that even though the order of the CTEs matters in this case (mycte3 needs to be defined after mycte1 and mycte2 since it references them), that's not because any of the CTEs is specially linked to the WITH.

You cannot write CTEs without the WITH.

You can read more in the documentation for CTEs/WITH.