CTE with UNION ALL

CTE with UNION ALL is a SQL statement in the CTE (WITH) category. CTEs can be combined with set operations. The CTE acts as a reusable query building block. The syntax is WITH cte AS (SELECT ...) SELECT * FROM cte UNION ALL SELECT * FROM other;. It returns cTE-powered combined result. A typical example: WITH active_users AS ( SELECT id, name, email FROM users WHERE status = 'active' ), recent_signups AS ( SELECT id, name, email FROM users WHERE created_at > NOW() - INTERVAL '30 days' ) SELECT id, name, email, 'Active' AS source FROM active_users UNION ALL SELECT id, name, email, 'New' FROM recent_signups ORDER BY name; -- Combine results from two CTEs into one report A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures).