Guru: Conditional SQL Unions

July 17, 2017 Ted Holt

SQL unions combine two or more result sets into one. That’s what they were designed to do. But unions also provide a way to choose between alternate result sets, i.e. to enable or disable SELECT statements at run time. I have used this feature to advantage on numerous occasions. Here’s how it’s done.

First consider the nature of unions. Each result set of a union can return data or no data, depending on the criteria in the WHERE and HAVING clauses. If a SELECT retrieves no data, the system appends an empty set to the union result. The way to turn a SELECT on or off, then, is to control the selection criteria in the WHERE and HAVING clauses.

As always, we need an example. Let’s start with a simple query. Let’s make a list of customers in Minnesota and New York. This would be a very easy query.

select State, City, CusNum, LstNam
  from qiws.qcustcdt
 where State in ('MN', 'NY')

If we have customers in either or both of those states, we get a result set.

If we have no customers in those states, then no rows (records) match the WHERE clause and we get an empty set.

Suppose that the result set, whether empty or populated, loads a grid in a GUI form. If the result set contains data, the user gets a list of customers. If the result set is empty, let’s be considerate and give the user a message. We don’t want people to sit around wondering if the computer is ever going to process their request or not.

with Selected as
   (select State, City, char(cusnum) as CusNum, LstNam
      from qiws.qcustcdt
     where State in ('MN', 'NY'))
select State, City, CusNum, LstNam
  from (select State, City, CusNum, LstNam
          from Selected     
         union all
        select 'No data', ' ', ' ', ' '
          from Selected
        having count(*) = 0 ) as x (State, City, CusNum, LstNam)
order by State

Look at the difference. I moved the SELECT into a common table expression called Selected.

with Selected as
   (select State, City, char(cusnum) as CusNum, LstNam
      from qiws.qcustcdt
     where State in ('MN', 'NY'))

I chose to convert the number account number to character. That’s not necessary, but it will make the union a little smoother.

Now query the common table expression:

select State, City, CusNum, LstNam
  from (select State, City, CusNum, LstNam
          from Selected     
         union all
        select 'No data', ' ', ' ', ' '
          from Selected
        having count(*) = 0 ) as x (State, City, CusNum, LstNam)
order by State

The FROM clause unions two SELECT statements. The first reads the data in Selected, and so may or may not return a result set. The second one returns a result set of one row if the common table expression is empty.

select 'No data', ' ', ' ', ' '
  from Selected
having count(*) = 0

This statement may look a little funny to you. It has a HAVING clause without a GROUP BY. In such a case, the entire input set (here, common table expression Selected) is treated as one group, and the SELECT clause can only have literals and aggregate functions. If it bothers you that GROUP BY is missing, you can add this one:

group by ()

If the result set comes up empty, the grid shows this:

The effect is that one, and only one, of the two SELECT statements returns data.

Works for me.

RELATED STORIES

The State of the UNION

Grouping a Union

Using UNION for Data Analysis

SQL Facts Of UNION And ORDER BY