SET Operations

UNION, INTERSECT, EXCEPT

UNION, INTERSECT and EXCEPT are set operations used to append similar data from disparate sources in a single SQL statement. These set operations are necessary when defining partitions and Union Views in the Omnidex environment file.

• The UNION set operation "unions" or appends two result sets from two separate SQL SELECT statements together, to form a single result set. The ALL keyword causes duplicate values to be repeated. For example:
  left : {1,3,5,7} right : {2,3,4,5}
  union : {1,2,3,4,5,7}
  union all : {1,2,3,3,4,5,5,7}
• The INTERSECT set operation creates a single result set comprised of values that exist in both result sets from two separate SQL SELECT statements. Values that exist in one result set but not the other, are discarded.
  left : {1,3,5,7} right : {2,3,4,5}
  intersect : {1,3,5}
• The EXECEPT set operation creates a single result set comprised of values that exist ONLY in the left SQL SELECT result set. Values that exist in both result sets are discarded, as are all values that exist only in the right result set.
  left : {1,3,5,7} right : {2,3,4,5}
  except : {1,7}

Syntax

sql_select_statement UNION [ALL] sql_select_statement

sql_select_statement INTERSECT sql_select_statement

sql_select_statement EXCEPT sql_select_statement

Limitations

• When using set operations, an ORDER BY clause must be only on the last member of the set. The sort order is then applied to the entire set. This limitation is a SQL 2 standard restriction, not specific to Omnidex.
• Each individual member of a set operation is optimized. However, there is no optimization across members of the set.

Examples

The next few examples demonstrate the difference in results between UNION, UNION ALL, INTERSECT, and EXCEPT. All examples use the same sql select statements, but the results differ greatly depending on the SET operation used.

This first example selects distinct account numbers from both the Activity and Orders tables, which are then combined with a UNION ALL statement. The ALL keyword causes duplicate account numbers to be repeated. Specifically, the account number 1 occurs in both the Activity and Orders table and is therefore returned twice. The text 'Activity' and 'Orders' are included in the select statement to demonstrate which table the values came from.

SELECT DISTINCT 'Activity', acct FROM activity WHERE tickler='RC'
UNION ALL
SELECT DISTINCT 'Orders', acct FROM orders WHERE quantity < 3

5 rows returned

Like the first example, this example selects distinct account numbers from both the Activity and Orders tables, which are then combined with a UNION statement. The ALL keyword is omitted, causing only distinct values to be returned. The account number 1, which occurs in both the Activity and Orders tables, is only returned once.

SELECT DISTINCT acct FROM activity WHERE tickler='RC'
UNION
SELECT DISTINCT acct FROM orders WHERE quantity < 3

ACCT
----------
1
2
3
4

4 rows returned

This next example INTERSECTS the results of the same select statements. This causes only one row to be returned, the account number 1, because this is the only account number that occurs in both the Activity and Orders tables.

SELECT DISTINCT acct FROM activity WHERE tickler='RC'
INTERSECT
SELECT DISTINCT acct FROM orders WHERE quantity < 3

ACCT
----------
1

1 row returned

The last two examples use the EXCEPT set operation, which causes only those rows from the first table that do not have matching rows in the second table, to be returned. As we saw in the first example (UNION ALL) above, there are two rows in the Activity table with account numbers 1 and 4, and three rows in the Orders table with account numbers 1, 2, 3.

In this case, the Activity table is first and the Orders table is second. Since account number 4 is the only account number in the Activity table that does not occur in the Orders table, the row with account number 4 is the only row returned.

SELECT DISTINCT acct FROM activity WHERE tickler='RC'
EXCEPT
SELECT DISTINCT acct FROM orders WHERE quantity < 3

ACCT
----------
4

1 row returned

Now if we switch the order of the select statements, the Orders table is first and the Activity table is second. Account number 1 occurs in both tables so is discarded while, account numbers 2 and 3 occur in the first table (Orders) but not in the second table (Activity), and are therefore returned.

SELECT DISTINCT acct FROM orders WHERE quantity < 3
EXCEPT
SELECT DISTINCT acct FROM activity WHERE tickler='RC'

ACCT
----------
2
3

2 rows returned

The following example shows a UNION ALL statement used to declare the partitioned ORDERS table in an Omnidex Environment file.

Top