Informix Guide to SQL: Tutorial Composing Advanced SELECT Statements

The standard set operations union, intersection, and difference let you manipulate database information. These three operations let you use SELECT statements to check the integrity of your database after you perform an update, insert, or delete. They can be useful when you transfer data to a history table, for example, and want to verify that the correct data is in the history table before you delete the data from the original table.

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.

You cannot use a UNION operator inside a subquery.

Dynamic Server does not support ordering on row types. Because a union operation requires a sort to remove duplicate values, you cannot use a UNION operator when either query in the union operation includes row type data. However, the database server does support UNION ALL with row type data since this type of operation does not require sort.

Figure 5-1 illustrates the union set operation.

Figure 5-1 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 select 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 nulls.

Query 5-35 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  

Query 5-35 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 Query Result 5-35 shows.

Query Result 5-35

As Query 5-36 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 Query 5-36 selects the same rows as Query 5-35 but displays them in order of the manufacturer code, as Query Result 5-36 shows.

Query Result 5-36

By default, the UNION keyword excludes duplicate rows. To retain the duplicate values, add the optional keyword ALL, as Query 5-37 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  stockitem

Query 5-37 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 Query 5-36 but also includes duplicate values.

Query Result 5-37

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

Query 5-38 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'

Query Result 5-38 returns state code abbreviations for customer numbers 120 through 125 and for states whose sname ends in a.

Query Result 5-38

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

Query 5-39 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

Query 5-39 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. Query Result 5-39 shows the return values.

Query Result 5-39

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

Query 5-40 uses a combined query to select data into a temporary table and then adds a simple query to order and display it. You must separate the combined and simple queries with a semicolon.

The combined query uses a literal in the select list to tag the output of part of a union so it can be distinguished later. The tag is given the label sortkey. The simple query uses that tag as a sort key 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

Query 5-40 creates a list in which the most frequently called customers, those from California, appear first, as Query Result 5-40 shows.

Query Result 5-40

The intersection of two sets of rows produces a table that contains rows that exist in both the original tables. Use the keyword EXISTS or IN to introduce subqueries that show the intersection of two sets. Figure 5-2 illustrates the intersection set operation.

Figure 5-2 The Intersection Set Operation

Query 5-41 is an example of a nested SELECT statement that shows the intersection of the stock and items tables.

SELECT stock_num, manu_code, unit_price

	FROM stock

	WHERE stock_num IN

		(SELECT stock_num FROM items)

	ORDER BY stock_num

Query Result 5-41 contains all the elements from both sets and returns the following 57 rows.

Query Result 5-41

The difference between two sets of rows produces a table that contains rows in the first set that are not also in the second set. Use the keywords NOT EXISTS or NOT IN to introduce subqueries that show the difference between two sets. Figure 5-3 illustrates the difference set operation.

Figure 5-3 The Difference Set Operation

Query 5-42 is an example of a nested SELECT statement that shows the difference between the stock and items tables.

SELECT stock_num, manu_code, unit_price

	FROM stock

	WHERE stock_num NOT IN

		(SELECT stock_num FROM items)

	ORDER BY stock_num

Query Result 5-42 contains all the elements from only the first set, which returns 17 rows.

stock_num  manu_code  unit_price  



            102  PRC                    $480.00

            102  SHM                    $220.00

            106  PRC                      $23.00


            312  HRO                      $72.00

            312  SHM                      $96.00

            313  ANZ                      $60.00

            313  SHM                      $72.00