Set Operations
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.
Union
The union operation uses the UNION keyword, or operator, to combine two queries into a single compound query. You can use the UNION keyword 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 cannot use a UNION operator inside a subquery or in the definition of a view.) Figure 3-1 illustrates 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 the same data type (CHARACTER data type columns must be the same length), and these corresponding columns must all allow or all disallow nulls.
Query 3-36 performs a union on the stock_num and manu_code columns in the stock and items tables.
Query 3-36
Query 3-36 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 3-36 shows.
If you include an ORDER BY clause, it must follow Query 3-36 and use an integer, not an identifier, to refer to the ordering column. Ordering takes place after the set operation is complete.
Query 3-37
The compound query in Query 3-37 selects the same rows as Query 3-36 but displays them in order of the manufacturer code, as Query Result 3-37 shows.
By default, the UNION keyword excludes duplicate rows. Add the optional keyword ALL, as Query 3-38 shows, to retain the duplicate values.
Query 3-38
Query 3-38 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. If you query with SELECT * from that table, you see the results as shown in Query Result 3-38. Query Result 3-38 includes duplicate values that are not shown in Query Result 3-37.
Corresponding columns in the select lists for the combined queries must have identical data types, but the columns do not need to use the same identifier.
Query 3-39 selects the state column from the customer table and the corresponding code column from the state table.
Query 3-39
Query Result 3-39 returns state code abbreviations for customer numbers 120 through 125 and for states whose sname ends in a .
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 3-40, the column name state from the first SELECT statement is used instead of the column name code from the second.
Query 3-40 performs a union on three tables. The maximum number of unions depends on the practicality of the application and any memory limitations.
Query 3-40
Query 3-40 selects items where the unit_price in the stock table is greater than $600.00, 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 3-40 shows the return values.
For the complete syntax of the SELECT statement and the UNION operator, see Chapter 1 of the Informix Guide to SQL: Syntax. For information specific to INFORMIX-ESQL/C and any limitations that involve the INTO clause and compound queries, see Chapter 5, "Programming with SQL,"and Chapter 6, "Modifying Data Through SQL Programs,"as well as the product manuals.
Query 3-41 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 for ordering the retrieved rows.
Query 3-41
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 3-41 creates a list in which the most frequently called customers, those from California, appear first, as Query Result 3-41 shows.
Intersection
The intersection of two sets of rows produces a table containing 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 3-2 illustrates the intersection set operation.
Query 3-42 is an example of a nested SELECT statement that shows the intersection of the stock and items tables.
Query 3-42
Query Result 3-42 contains all the elements from both sets.
Difference
The difference between two sets of rows produces a table containing 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 3-3 illustrates the difference set operation.
Query 3-43 is an example of a nested SELECT statement that shows the difference between the stock and items tables.
Query 3-43
Query Result 3-43 contains all the elements from only the first set, which returns 17 rows.
|