Progress
SQL-92
Guide and Reference
Set Operator
This is the syntax for the set_operator:
UNION [ ALL ]
Appends the result table from one query expression to the result table from another.
The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types.
The final result table contains the rows from the second query expression appended to the rows from the first. By default, the result table does not contain any duplicate rows from the second query expression. Specify
UNION
ALL
to include duplicate rows in the result table.The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types.
INTERSECT
Limits rows in the final result table to those that exist in the result tables from both query expressions.
The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types:
MINUS
Limits rows in the final result table to those that exist in the result table from the first query expression minus those that exist in the second. In other words, the
MINUS
operator returns rows that exist in the result table from the first query expression but that do not exist in the second.The two query expressions must have the same number of columns in their result tables, and those columns must have the same or compatible data types.
EXAMPLESThe following example specifies all columns in the customer table:
The
table_name.
* syntax is useful when the select list refers to columns in multiple tables and you want to specify all the columns in one of those tables:
You can qualify a column name with the name of the table it belongs to. The following examples illustrate column names qualified by table name:
The following example illustrates specifying a new column heading for an associated column. Enclose the new title in single or double quotation marks if it contains a space or other special characters:
You must qualify a column name if it occurs in more than one table specified in the
FROM
clause:
The following example selects only the customers whose name is ’SMITH’:
The following example finds all customers whose city is the same as the customer ’SMITH’.
The following example retrieves the customer and order information for customers with orders:
The
HAVING
clause in the following example compares the value of an aggregate function
(COUNT (*)
) to a constant (10
). The query returns the customer number and number of orders for all customers who had more than 10 orders before March 31st, 1999.
The following examples show merging a list of customers and suppliers without and with duplicate entries:
The following example illustrates the
INTERSECT
option:
The following example illustrates the
MINUS
option:
AUTHORIZATION
SQL COMPLIANCE
SQL-92; the MINUS operator is equivalent to the SQL-92 EXCEPT operator
ENVIRONMENT
Embedded SQL, interactive SQL, ODBC applications
RELATED STATEMENTS
CREATE TABLE Statement, CREATE VIEW Statement, INSERT Statement, "Search Conditions," SELECT Statement, UPDATE Statement
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |