# Teradata UNION ALL? I’ll show you something better that makes a query 50% faster!

Roland Wenzlofsky

December 31, 2019

minutes reading time

# 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:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like