Best Substitute For Union ALL

[chrisrlong]

It depends very much on what the individual SELECT statements are that are being UNIONed. If, for example, one has:

SELECT
	COL1	,
	COUNT(*)
FROM
	TABLE1
WHERE
	COL1	=	5
UNION ALL
SELECT
	COL1	,
	COUNT(*)
FROM
	TABLE1
WHERE
	COL1	=	4

...then it should obviously be collapsed to:

SELECT
	COL1		,
	COUNT(*)
FROM
	TABLE1
WHERE
	COL1	IN	(4,5)
GROUP BY
	COL1

If, however, you are UNIONing SELECts from 2 completely un-related tables, then the UNION ALL wins.

This can be abstracted to a larger rule: Always factor out similar tables from a UNION. In set notation, (ac)U(bc) => c(aUb). This will allow the table c to be accessed only once instead of twice. Of course, this should only be applied where c is a lookup (non-restrictive) table.

- Chris