Is column order guaranteed in SELECT * also within a CTE

March 13, 2024

This was already answered for queries taking directly from a table, where there is an ordinal_position metadata to draw from. My question is whether the same applies for CTEs (which may or may not have their own ordinal_position, I genuinely do not know).

WITH
cte_a AS (
SELECT 
    col_a_1 AS col_1,
    col_a_2 AS col_2
FROM table_a
),

cte_b AS (
SELECT 
    col_b_1 * 10 AS col_1,
    0            AS col_2
FROM table_b
),

unioned AS (
SELECT * FROM cte_a
UNION ALL
SELECT * FROM cte_b
),
-- more CTEs follow
-- Ultimately, a closing SELECT follows

Are the two SELECT * above guaranteed to preserve the order defined in the CTEs they draw from?

>Solution :

The order of columns would not change, the CTE "inherits" the metadata for the ordinal position.
Think of a CTE in this case as a regular view, views have their ordinal positions too.