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;

Filed under: DB2 Tagged: CASE instead of UNION, DB2 Tips and Solutions