Previous Topic

Next Topic

Book Contents

Book Index

Advanced Union Queries

Union Operator

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

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:

These are managed on the Union tab.