SQL Question

Hi
One for the SQL gurus! I'm using HFCS but the question stands for all SQL databases I guess.

Is a query using UNION/UNION ALL more or less efficient than a query giving the same results by using CASE statements. I originally wrote the query using UNION but then started thinking (rather dangerous sometimes!) & came up with the 2nd option, which gives me exactly the same result. My dataset is currently quite small so there is no appreciable difference but the files could end up with several million records. Appropriate indexes etc are in place.

Thanks

David

Original

	SELECT RegisterId,userid,'SALES' AS TranType,COUNT(*) AS Qty,SUM(salevalue) AS amount, 1 AS sequence

	FROM salhead 

	WHERE BranchId = '%1'	

	AND shiftnum BETWEEN  '%2' AND '%3'

	AND trantype = 'SALE'

	AND status  'V'

	AND salevalue > 0

	GROUP BY RegisterId,userid

	UNION

	SELECT RegisterId,userid,'VOIDS' AS TranType,COUNT(*) AS Qty,0 AS amount,

	2 AS sequence

	FROM salhead

	WHERE BranchId = '%1'

	AND shiftnum BETWEEN '%2' AND '%3'

	AND trantype = 'SALE'

	AND status = 'V'

	GROUP BY RegisterId,userid

	UNION

	SELECT RegisterID,userid,'RETURNS' AS TranType,COUNT(*) AS Qty,SUM(salevalue) AS amount, 3 AS sequence

	FROM salhead

	WHERE BranchId = '%1'

	AND shiftnum BETWEEN '%2' AND '%3'

	AND trantype = 'SALE'

	AND status  'V'

	AND salevalue < 0

	GROUP BY RegisterId,userid

The alternative:

SELECT RegisterId,userid,

CASE

    WHEN Status = '' AND SaleValue > 0 THEN 'SALES'

    WHEN Status = '' AND SaleValue < 0 THEN 'RETURNS'

    WHEN Status = 'V' THEN 'NOSALES'

    WHEN status = 'H' THEN 'HELD'

    ELSE 'ACTIVE' 

END AS TranDesc,

COUNT(*) AS Qty,SUM(salevalue) AS amount, 

CASE

    WHEN Status = '' AND SaleValue > 0 THEN '1'

    WHEN Status = '' AND SaleValue < 0 THEN '3'

    WHEN Status = 'V' THEN '2'

    WHEN status = 'H' THEN '90'

    ELSE '95' 

END AS sequence

FROM salhead 

WHERE BranchId = '%1'	

    AND shiftnum BETWEEN '%2' AND '%3'

    AND trantype = 'SALE'

    AND ABS(salevalue) > 

        CASE WHEN Status = 'V' THEN (salevalue - 1) ELSE 0 END

GROUP BY RegisterId, userid,Trandesc,sequence