Guru: SQL Facts Of UNION And ORDER BY

April 10, 2017 Ted Holt

UNION and ORDER BY are powerful SQL features, but put the two together and you may get some strange and frustrating error messages. Fortunately for us DB2 for i professionals, there are easy ways to make the two collaborate and cooperate. Today is a great day to be sure we understand them.

We need some data for examples. For some of the queries, I use a customer master table (file) and a vendor master table. These are a company’s trading partners, so the two have many attributes in common, especially names and addresses. I also use two sales history tables, one each for the years 2015 and 2016. Businesses commonly use such tables to speedily access summary data that does not change. Such a table would have the same data in various formats (e.g. monthly sales, quarterly sales, yearly sales). In these examples, I use the yearly sales.

The following tables show the column (field) names for these database tables.

Fact 1: ORDER BY follows the last subselect and applies to the entire union.

A common mistake that people make is to put an ORDER BY clause on each subselect. Doing so is not allowed because it is unnecessary. The system combines the result sets from all the subselects, then sorts the combined data. In this example, trading partners are retrieved in order by city within state.

select state, city, name, custnbr 
  from cust                     
union all                         
select state, city, name, vendorid
  from vendor
order by state, city              

Fact 2: You can sort on column names that are in every component result sets. When combining data for trading partners, for example, you can sort on NAME, CITY, and STATE.

select custnbr, name, city, state, zip
  from cust
union all
select vendorid, name, city, state, zipcode
  from vendor
order by name

Fact 3: If a column has different names in different subselects, you must rename one or more of them in order to generate an acceptable column name. In this query, I rename the ZIPCODE column of the vendor table to ZIP.

select custnbr, name, city, state, zip
  from cust
union all
select vendorid, name, city, state, zipcode as zip
  from vendor
order by zip

Fact 4: You may use column numbers in the ORDER BY clause. (This is one of my favorite ways to sort a union.) In this case, the columns do not have to have consistent names across all the subselects. In this example, I order the data by the fifth column, which is ZIP in the customer table and ZIPCODE in the vendor table.

select custnbr, name, city, state, zip
  from cust
union all
select vendorid, name, city, state, zipcode
  from vendor
order by 5

Fact 5: When using UNION to create a table, you can’t use column numbers. Instead, you must be sure to name the columns consistently across all subselects. There are two ways to do so.

The first way is to use correlation names.

create table mylib.tpwork
 as
(select custnbr as ID, name, city, state, zip
  from cust
union all
select vendorid as ID, name, city, state, zipcode as zip
  from vendor)
with data

The second way is to list the columns in the CREATE TABLE command.

create table mylib.tpwork (ID, name, city, state, zip)
 as
(select custnbr, name, city, state, zip
  from cust
union all
select vendorid, name, city, state, zipcode
  from vendor)
with data

Fact 6: You can use ORDER BY in the component SELECTs by enclosing the expressions in parentheses. The only time this makes sense is when you need an ORDER BY to help with row selection. Find the top two customers for the years 2015 and 2016.

 (select * from sales2015  
  order by yearly desc    
  fetch first 2 rows only)
union all
(select * from sales2016  
  order by yearly desc    
  fetch first 2 rows only)

As with any union, we can add another ORDER BY clause for the entire union to sort the final result set.

 (select * from sales2015  
  order by yearly desc    
  fetch first 2 rows only)
union                     
(select * from sales2016  
  order by yearly desc    
  fetch first 2 rows only)
order by yearly desc      

Fact 7: You can’t use expressions when ordering a union. (This is a bummer.)

select custnbr, name, city, state, zip           
  from bbcust                                    
union all                                        
select vendorid, name, city, state, zipcode      
  from bbvendor                                  
order by case when state = 'MN' then 0 else 1 end

The system heartlessly responds with the message “ORDER BY expression is not valid.” Maybe someday.  I can dream.

Fact 8: These principles also apply to INTERSECT and EXCEPT. I did not include examples of those because unlike UNION, I don’t find them very useful.

The bottom line: Don’t panic when you see those goofy error messages. In most cases, you can find a way around them.