Union

The union operation uses the UNION keyword, or operator, to combine two queries into a single compound query. You can use the UNION operator between two or more SELECT statements to unite them and produce a temporary table that contains rows that exist in any or all of the original tables. You can also use the UNION operator in the definition of a view.

Figure 329 illustrates the UNION set operation.

Figure 329. The Union Set Operation

The UNION keyword selects all rows from the two queries, removes duplicates, and returns what is left. Because the results of the queries are combined into a single result, the projection list in each query must have the same number of columns. Also, the corresponding columns that are selected from each table must contain compatible data types (CHARACTER data type columns must be the same length), and these corresponding columns must all allow or all disallow NULL values.

For the complete syntax of the SELECT statement and the UNION operator, see the IBM Informix: Guide to SQL Syntax. For information specific to the IBM Informix ESQL/C product and any limitations that involve the INTO clause and compound queries, see the IBM Informix: ESQL/C Programmer's Manual.

Figure 330 performs a union on the stock_num and manu_code columns in the stock and items tables.

SELECT DISTINCT stock_num, manu_code FROM stock
   WHERE unit_price < 25.00
UNION
SELECT stock_num, manu_code FROM items
   WHERE quantity > 3 

Figure 330 selects those items that have a unit price of less than $25.00 or that have been ordered in quantities greater than three and lists their stock_num and manu_code, as Figure 331 shows.

stock_num manu_code

        5 ANZ
        5 NRG
        5 SMT
        9 ANZ
      103 PRC
      106 PRC
      201 NKL
      301 KAR
      302 HRO
      302 KAR

Using ORDER BY with UNION

As Figure 332 shows, when you include an ORDER BY clause, it must follow the final SELECT statement and use an integer, not an identifier, to refer to the ordering column. Ordering takes place after the set operation is complete.

SELECT DISTINCT stock_num, manu_code FROM stock
   WHERE unit_price < 25.00
UNION
SELECT stock_num, manu_code FROM items
   WHERE quantity > 3 
   ORDER BY 2

The compound query in Figure 332 selects the same rows as Figure 330 but displays them in order of the manufacturer code, as Figure 333 shows.

stock_num manu_code

        5 ANZ
        9 ANZ
      302 HRO
      301 KAR
      302 KAR
      201 NKL
        5 NRG
      103 PRC
      106 PRC
        5 SMT

Using UNION ALL

By default, the UNION keyword excludes duplicate rows. To retain the duplicate values, add the optional keyword ALL, as Figure 334 shows.

SELECT stock_num, manu_code FROM stock
   WHERE unit_price < 25.00
UNION ALL
SELECT stock_num, manu_code FROM items
   WHERE quantity > 3 
   ORDER BY 2
   INTO TEMP stock item

Figure 334 uses the UNION ALL keywords to unite two SELECT statements and adds an INTO TEMP clause after the final SELECT to put the results into a temporary table. It returns the same rows as Figure 332 but also includes duplicate values.

stock_num manu_code

        9 ANZ
        5 ANZ
        9 ANZ
        5 ANZ
        9 ANZ
       
·
·
·

        5 NRG
        5 NRG
      103 PRC
      106 PRC
        5 SMT
        5 SMT

Using Different Column Names

Corresponding columns in the projection clauses for the combined queries must have compatible data types, but the columns do not need to use the same column names.

Figure 336 selects the state column from the customer table and the corresponding code column from the state table.

SELECT DISTINCT state FROM customer
   WHERE customer_num BETWEEN 120 AND 125
UNION
SELECT DISTINCT code FROM state
   WHERE sname MATCHES '*a'

Figure 337 returns state code abbreviations for customer numbers 120 through 125 and for states whose sname ends in a.

state

AK
AL
AZ
CA
DE

·
·
·

SD
VA
WV

In compound queries, the column names or display labels in the first SELECT statement are the ones that appear in the results. Thus, in Figure 336, the column name state from the first SELECT statement is used instead of the column name code from the second.

Using UNION with Multiple Tables

Figure 338 performs a union on three tables. The maximum number of unions depends on the practicality of the application and any memory limitations.

SELECT stock_num, manu_code FROM stock
   WHERE unit_price > 600.00
UNION ALL
SELECT stock_num, manu_code FROM catalog
   WHERE catalog_num = 10025
UNION ALL
SELECT stock_num, manu_code FROM items
   WHERE quantity = 10
   ORDER BY 2

Figure 338 selects items where the unit_price in the stock table is greater than $600, the catalog_num in the catalog table is 10025, or the quantity in the items table is 10; and the query orders the data by manu_code. Figure 339 shows the return values.

stock_num manu_code

        5 ANZ
        9 ANZ
        8 ANZ
        4 HSK
        1 HSK
      203 NKL
        5 NRG
      106 PRC
      113 SHM

Using a Literal in the Projection Clause

Figure 340 uses a literal in the projection list to tag the output of part of a union so it can be distinguished later. The tag is given the label sortkey. The query uses sortkey to order the retrieved rows.

SELECT '1' sortkey, lname, fname, company, 
     city, state, phone 
   FROM customer x
   WHERE state = 'CA'
UNION
SELECT '2' sortkey, lname, fname, company, 
     city, state, phone 
   FROM customer y
   WHERE state <> 'CA'
   INTO TEMP calcust;
SELECT * FROM calcust
   ORDER BY 1

Figure 340 creates a list in which the customers from California appear first, as Figure 341 shows.

sortkey  1
lname    Baxter
fname    Dick
company  Blue Ribbon Sports
city     Oakland
state    CA
phone    415-655-0011

sortkey  1
lname    Beatty
fname    Lana
company  Sportstown
city     Menlo Park
state    CA
phone    415-356-9982

·
·
·

sortkey  2
lname    Wallack
fname    Jason
company  City Sports
city     Wilmington
state    DE
phone    302-366-7511

Using a FIRST Clause

Extended Parallel Server allows you to use the FIRST clause to select the first rows that result from a union query. Figure 342 uses a FIRST clause to return the first five rows of a union between the stock and items tables.

SELECT FIRST 5 DISTINCT stock_num, manu_code
   FROM stock
   WHERE unit_price < 55.00
UNION 
SELECT stock_num, manu_code
   FROM items
   WHERE quantity > 3

stock_num manu_code

        5 NRG 
        5 ANZ
        6 SMT
        6 ANZ
        9 ANZ