Advanced Union Queries

Union Operator

There are a number of different operators that can be used in Union queries

• Union. Combine the results of the statements, with duplicates removed
• Union All. Combine the results of the statements, with duplicates retained
• Intersect. Only include rows which are returned by both statements.
• Except. Include rows which are returned by the first statement, excluding those returned by the second.

Intersect and Except are only available for some databases. Oracle uses Minus rather than Except.

Example 1

To see which customers had sales in both 2016 and 2017, run the query.

Select distinct CUSTOMER from SALES_DETAILS_2016

Intersect

Select distinct CUSTOMER from SALES_DETAILS_2017

Example 2

To see which customers had sales in 2016 but not in 2017, run the query

Select distinct CUSTOMER from SALES_DETAILS_2016

Except

Select distinct CUSTOMER from SALES_DETAILS_2017

Braketing

When Intersect and Except are used, the order of the statements becomes important. In this case, statements can be braketed to force them to be processed in the appropriate order.

For instance, to see customers that had sales in both 2015 and 2016, but not in 2017, run

(Select distinct CUSTOMER from SALES_DETAILS_2015

Intersect

Select distinct CUSTOMER from SALES_DETAILS_2016)

Except

Select distinct CUSTOMER from SALES_DETAILS_2017

This gives different results if the brakets were different or not present.

AQT Support of advanced Union Queries

AQT can deal with queries such as this, as:

• it supports the use of different Union operators
• it supports the use of braketing

These are managed on the Union tab.