The Idea Behind This Trick for Teradata UNION ALL

What if you want to perform a UNION ALL on two different columns of the same table? Normally you would do this:

SELECT CloseDate FROM Customer
UNION ALL
SELECT OpenDate FROM Customer
;

However, this method has a disadvantage:

The table Customer must be queried with two full table scans. So I looked for a method that works with only one full table scan and without UNION ALL but gives the same result.

A possible solution I found is the following:

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)
;

This query needs only one full table scan and saves a lot of IOs, especially for huge tables.

As you can see, the number of IOs has been reduced from 10146110.00 to 5521013.00, almost halved!

The Same Trick for UNION

UNION can easily be simulated by using DISTINCT in the column selection list:

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)
;

Here is the query which uses the UNION functionality:

SELECT CloseDate FROM Customer
UNION
SELECT OpenDate FROM Customer
;

Unfortunately, I can’t provide concrete numbers here because the UNION variant aborted with a “no more spool space” error. This happens because the query is skewed.

But the results should be similar.

See also: