When a UNION is required to put together data from multiple queries, you might be able to use a CASE statement instead. This is very useful, particularly when the data for each of the queries in the UNION come from the same table. The CASE statement can potentially enhance performance by minimizing the number of times the data is read.
Let’s look at an example to clarify why:
SELECT CREATOR, NAME, ‘TABLE’
WHERE TYPE = ‘T’
SELECT CREATOR, NAME, ‘VIEW ‘
WHERE TYPE = ‘V’
SELECT CREATOR, NAME, ‘ALIAS’
WHERE TYPE = ‘A’
ORDER BY NAME;
This simple SQL statement uses UNION to put together the results of three queries against the SYSTABLES table. The report shows all of the DB2 table-like objects that exist in the DB2 subsystem: tables, views, and synonyms.
To do this, DB2 must scan through the table three times – once for each query (as there is no index on the TYPE column). But, you can use CASE and code an equivalent, but more efficient query, as follows:
SELECT CREATOR, NAME,
WHEN ‘T’ THEN ‘TABLE’
WHEN ‘V’ THEN ‘VIEW ‘
WHEN ‘A’ THEN ‘ALIAS’
ORDER BY NAME;
This new query will need to scan SYSTABLES only once. The CASE statement will translate the code in the TYPE column into the text that we desire.
CASE statements are very powerful and you should use them when you can to create elegant and optimal SQL in your DB2 applications.
This post was originally published on Craig Mullins’ blog at: http://db2portal.blogspot.com/2009/11/replacing-union-with-case.html
For additional resources please download my white paper: “The Many Different Types of DBAs”
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.