The Hidden Risk of Inline UNION ALL in SQL (and Better Alternatives)

How to write safer, more maintainable queries.

📖 This article is part of my series on SQL Best Practices for Clear & Concise Data Transformation. Each post tackles a common anti-pattern and shows a cleaner way to write queries that are easier to test, reuse, and maintain.

Summary

Ever opened a SQL query and seen a giant inline UNION ALL crammed inside a FROM clause — complete with casts, filters, and calculations sprinkled in? It might work, but it’s messy, hard to test, and even harder to maintain. One wrong bracket or type mismatch, and you’re debugging for hours.

A better way: keep the union clean. Push all the prep work into per-source CTEs, then combine them in a single, pure union step. That small change makes queries clearer, safer, and much easier to extend.

Press enter or click to view image in full size

Why this matters

A UNION ALL should do exactly one thing: union data.

When you mix in casts, filters, deduping, or new columns inside the union itself, you blur responsibilities. That makes queries harder to debug, more brittle, and less reusable.

The fix is simple:
👉 Do all preparation work in per-source CTEs, then keep the **UNION ALL** pure.

Why it helps

• Separation of concerns → each step has one clear responsibility.
• Testability → you can SELECT * from each *_prepared CTE independently.
• Consistency → all branches align to the same schema before the union.
• Planner clarity → filters and partitions stay close to their sources.

Anti-pattern: transforming inside the union

– ❌ Avoid: casting, filtering, and literals inside the union
WITH combined_orders AS (
SELECT order_id, customer_id, CAST(order_ts AS TIMESTAMP) AS order_ts,
revenue::NUMERIC AS revenue, ‘orders_2025’ AS _src
FROM bronze.orders_2025
WHERE order_ts >= DATE ‘2025-01-01’
UNION ALL
SELECT order_id, customer_id, CAST(order_ts AS TIMESTAMP),
revenue::NUMERIC, ‘legacy_orders’
FROM bronze.legacy_orders
WHERE order_ts >= DATE ‘2025-01-01’
)
SELECT * FROM combined_orders;

Best practice: pure union (prep done earlier)

WITH orders_2025_prepared AS (
SELECT order_id, customer_id,
CAST(order_ts AS TIMESTAMP) AS order_ts,
CAST(revenue AS NUMERIC) AS revenue,
‘orders_2025’ AS _src
FROM bronze.orders_2025
WHERE order_ts >= DATE ‘2025-01-01’
),
legacy_orders_prepared AS (
SELECT order_id, customer_id,
CAST(order_ts AS TIMESTAMP) AS order_ts,
CAST(revenue AS NUMERIC) AS revenue,
‘legacy_orders’ AS _src
FROM bronze.legacy_orders
WHERE order_ts >= DATE ‘2025-01-01’
),
combined_orders AS ( – ✅ pure union: no casts/filters/expressions
SELECT order_id, customer_id, order_ts, revenue, _src FROM orders_2025_prepared
UNION ALL
SELECT order_id, customer_id, order_ts, revenue, _src FROM legacy_orders_prepared
)
SELECT customer_id, SUM(revenue) AS total_rev
FROM combined_orders
GROUP BY customer_id;

Multi-source example: three-way events union

WITH web_events_prepared AS (
SELECT user_id, CAST(event_ts AS TIMESTAMP) AS event_ts,
event_name, ‘web’ AS _src
FROM raw.web_events
WHERE event_date >= CURRENT_DATE – 30
),
app_events_prepared AS (
SELECT user_id, CAST(event_ts AS TIMESTAMP) AS event_ts,
event_name, ‘app’ AS _src
FROM raw.app_events
WHERE event_date >= CURRENT_DATE – 30
),
pos_events_prepared AS (
SELECT user_id, CAST(event_ts AS TIMESTAMP) AS event_ts,
event_name, ‘pos’ AS _src
FROM raw.pos_events
WHERE event_date >= CURRENT_DATE – 30
),
combined_events AS ( – ✅ pure union
SELECT user_id, event_ts, event_name, _src FROM web_events_prepared
UNION ALL
SELECT user_id, event_ts, event_name, _src FROM app_events_prepared
UNION ALL
SELECT user_id, event_ts, event_name, _src FROM pos_events_prepared
),
ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS rn
FROM combined_events
)
SELECT user_id, event_ts, event_name, _src
FROM ranked
WHERE rn <= 5;

Handling schema drift before the union

WITH payments_v2_prepared AS (
SELECT payment_id, customer_id,
(amount_cents / 100.0)::NUMERIC AS amount,
currency,
CAST(settled_at AS TIMESTAMP) AS settled_at,
‘v2’ AS _src
FROM bronze.payments_v2
WHERE settled_at::DATE >= CURRENT_DATE – 90
),
payments_v1_prepared AS (
SELECT payment_id, customer_id,
CAST(amount AS NUMERIC) AS amount,
‘USD’ AS currency,
CAST(settled_at AS TIMESTAMP) AS settled_at,
‘v1’ AS _src
FROM bronze.payments_v1
WHERE settled_at::DATE >= CURRENT_DATE – 90
),
combined_payments AS (
SELECT payment_id, customer_id, amount, currency, settled_at, _src
FROM payments_v2_prepared
UNION ALL
SELECT payment_id, customer_id, amount, currency, settled_at, _src
FROM payments_v1_prepared
)
SELECT customer_id, SUM(amount) AS amount_90d
FROM combined_payments
GROUP BY customer_id;

Dedupe first, then prepare, then union

WITH v1_dedup AS (
SELECT * FROM (
SELECT , ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY _ingested_at DESC) AS rn
FROM bronze.payments_v1
) d
WHERE rn = 1
),
v2_dedup AS (
SELECT * FROM (
SELECT , ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY _ingested_at DESC) AS rn
FROM bronze.payments_v2
) d
WHERE rn = 1
),
v1_prepared AS (
SELECT payment_id, customer_id,
CAST(amount AS NUMERIC) AS amount,
CAST(settled_at AS TIMESTAMP) AS settled_at,
‘v1’ AS _src
FROM v1_dedup
WHERE settled_at >= CURRENT_DATE – INTERVAL ‘90 days’
),
v2_prepared AS (
SELECT payment_id, customer_id,
(amount_cents/100.0)::NUMERIC AS amount,
CAST(settled_at AS TIMESTAMP) AS settled_at,
‘v2’ AS _src
FROM v2_dedup
WHERE settled_at >= CURRENT_DATE – INTERVAL ‘90 days’
),
combined AS (
SELECT payment_id, customer_id, amount, settled_at, _src FROM v1_prepared
UNION ALL
SELECT payment_id, customer_id, amount, settled_at, _src FROM v2_prepared
)
SELECT customer_id, COUNT(*) AS n_payments, SUM(amount) AS total_amount
FROM combined
GROUP BY customer_id;

Joining after the union (final SELECT stays thin)

WITH a_prepared AS (
SELECT id, cust_id, CAST(ts AS TIMESTAMP) AS ts,
metric::NUMERIC AS metric, ‘A’ AS _src
FROM src.a
WHERE ts >= DATE ‘2025-01-01’
),
b_prepared AS (
SELECT id, cust_id, CAST(ts AS TIMESTAMP) AS ts,
metric::NUMERIC AS metric, ‘B’ AS _src
FROM src.b
WHERE ts >= DATE ‘2025-01-01’
),
combined_ab AS ( – ✅ pure union
SELECT id, cust_id, ts, metric, _src FROM a_prepared
UNION ALL
SELECT id, cust_id, ts, metric, _src FROM b_prepared
),
active_customers AS (
SELECT customer_id FROM dim.customer WHERE is_active = TRUE
)
SELECT c.cust_id, SUM(c.metric) AS metric_sum
FROM combined_ab c
JOIN active_customers ac ON ac.customer_id = c.cust_id
GROUP BY c.cust_id;

Quick checklist for “Pure UNION ALL”

Build per-source ***_prepared** CTEs that:

• Apply filters/partition predicates
• Cast to final types
• Normalize strings/categories
• Add lineage (_src, _load_at) if needed
• Dedupe if necessary

The union CTE:

• No expressions (no casts, no filters, no literals)
• Aligned schemas (same columns, order, and types)
• Explicit column lists (avoid SELECT *)
• Downstream: windowing, aggregations, joins.
• Final SELECT: keep it thin.

Wrap-up

By keeping UNION ALL pure, you make queries clearer, safer, and far more reusable.
Your future self (and your teammates) will thank you when debugging is as simple as inspecting a single *_prepared CTE.

📌 This article is Part 1 in the series SQL Best Practices for Clear & Concise Data Transformation.
👉 Continue with Part 2: Keep [**UNION ALL**](https://medium.com/@jacovanderlaan/exclude-inline-union-all-and-keep-it-pure-726ee867c014) Pure (Separation of Concerns)