This SQL query question asked me many times when I was attending for interviews. Why CASE statement is present in SQL.
By looking, it is not so complex but to tell in interviews you need solid answer. You can read many SQL tips and Tricks here.
The below example first they used with UNION. The example adopted from Carig mullins 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 statement, it is always better to use CASE statement.
Let me share an example, how it can be written into 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;