CASE Vs UNION Which is Better in SQL

Here is an example of SQL CASE and SQL Union. Here it shows which is better. Click here for SQL Tips and Tricks

SQL Union

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.

SQL 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;

Related Posts