Here is an example of SQL CASE and SQL Union. Here it shows which is better. Click here for SQL Tips and Tricks
Below is the SQL query used by UNION. The example adopted from Craig Mullin’s blog
SELECT CREATOR, NAME, 'TABLE' FROM SYSIBM.SYSTABLES WHERE TYPE = 'T' UNION SELECT CREATOR, NAME, 'VIEW ' FROM SYSIBM.SYSTABLES WHERE TYPE = 'V' UNION SELECT CREATOR, NAME, 'ALIAS' FROM SYSIBM.SYSTABLES WHERE TYPE = 'A' ORDER BY NAME;
In the above case, the lines of SQL are more, and it takes a lot of time to process it. So, whenever you want to use multiple UNION statements, it is always better to use a CASE statement. Let me share an example of how it can be re-write using CASE.
SELECT CREATOR, NAME, CASE TYPE WHEN 'T' THEN 'TABLE' WHEN 'V' THEN 'VIEW ' WHEN 'A' THEN 'ALIAS' END FROM SYSIBM.SYSTABLES ORDER BY NAME;