Optimize Teradata UNION ALL with a Single Table Scan Trick

The Idea Behind This Trick for Teradata UNION ALL

What if you need to apply a UNION ALL operation to distinct columns within a single table? Typically, the process would involve:

SELECT CloseDate FROM Customer
UNION ALL
SELECT OpenDate FROM Customer
;

The drawback of this method is that it scans the Customer table twice. To achieve the same output with just one full table scan, here is an alternative approach:

SELECT CASE WHEN t02.NBR = 1 THEN t01.CloseDate WHEN t02.NBR = 2 THEN t01.OpenDate ELSE NULL END FROM Customer t01 CROSS JOIN ( SELECT x AS NBR FROM (SELECT 1 AS x) x UNION ALL SELECT x AS NBR FROM (SELECT 2 AS x) x ) t02 WHERE t02.NBR IN (1,2) ;

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Launch access is open. The single paid plan will be EUR 49 per year.

Get the next issue

This query only requires one complete table scan and significantly reduces IOs, particularly for massive tables.

If you work with enterprise data platforms, migrations, performance tuning, or AI-driven delivery teams, DWHPro Letters is written for you. Get the next issue by email.

The IOs have significantly decreased from 10146110.00 to 5521013.00, nearly a 50% reduction.

The Same Trick for UNION

Adding DISTINCT simulates UNION through column selection.

SELECT DISTINCT CASE WHEN t02.NBR = 1 THEN t01.CloseDate WHEN t02.NBR = 2 THEN t01.OpenDate ELSE NULL END FROM Customer t01 CROSS JOIN ( SELECT x AS NBR FROM (SELECT 1 AS x) x UNION ALL SELECT x AS NBR FROM (SELECT 2 AS x) x ) t02 WHERE t02.NBR IN (1,2) ;

The equivalent standard UNION query looks like this:

SELECT CloseDate FROM Customer
UNION
SELECT OpenDate FROM Customer
;

I cannot provide specific figures as the UNION variant terminated due to a "no more spool space" error, likely caused by the query's skewness.

The results should be comparable to the DISTINCT-based approach above.

Additionally, refer to:

https://letters.dwhpro.com/content/files/2026/05/teradata-union-all.html

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe before the paid plan launches and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.