How to Use Set Operators in Oracle SQL

Joins combine tables side by side, adding columns. Set operators combine whole query results one on top of the other, adding or removing rows: all the airports from two lists, the airports that appear in both, or the countries in one list but not the other. Oracle SQL has four set operators, UNION, INTERSECT, MINUS, and its standard name EXCEPT, plus ALL versions of INTERSECT, MINUS, and EXCEPT that count duplicates.

Code for This Guide

The main examples are in the examples/subqueries folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

query1 {union | union all | intersect [all] | minus [all] | except [all]} query2
[order by ...]

The Rules

• Both queries must select the same number of columns, with compatible data types, matched by position.
• The column names of the result come from the first query, so put the aliases there.
• One ORDER BY at the very end sorts the combined result; it can use the first query's names or column positions.
• All set operators have equal precedence and run from left to right. Use parentheses to change the order.

A column-count mismatch is caught at parse time:

Example:

select airport_code, city from airports
union
select country_code from countries;

Output:

Error starting at line : 1
In command -
select airport_code, city from airports
union
select country_code from countries
Error at Command Line : 1 Column : 1
Error report -
SQL Error: ORA-01789: query block has incorrect number of result columns

UNION and UNION ALL

These queries combine the airports with a route to Singapore and the airports with a route from Sydney. UNION removes the duplicates; UNION ALL keeps them.

Example:

select origin as airport from routes where destination = 'SIN'
union
select destination from routes where origin = 'SYD'
order  by 1;

select origin as airport from routes where destination = 'SIN'
union all
select destination from routes where origin = 'SYD'
order  by 1;

Output:

AIRPORT
__________
AKL
DXB
NRT
SIN
SYD

AIRPORT
__________
AKL
DXB
DXB
NRT
SIN
SYD

6 rows selected.

DXB appears in both lists, so UNION shows it once and UNION ALL twice. UNION ALL is also faster, because it does not need to sort or hash the rows to find duplicates. Use it whenever duplicates are impossible or wanted.

INTERSECT, MINUS, and EXCEPT

INTERSECT returns the rows both queries return; here, the airports with routes both to and from Sydney. MINUS returns the rows of the first query that the second does not return; here, the country that has customers but no airport. EXCEPT is the SQL-standard name for MINUS and gives the same result.

Example:

-- airports that are both an origin and a destination of routes to or from Sydney
select origin from routes where destination = 'SYD'
intersect
select destination from routes where origin = 'SYD';

-- countries with customers but no airport
select country_code from customers
minus
select country_code from airports;

select country_code from customers
except
select country_code from airports;

Output:

ORIGIN
_________
AKL
DXB
SIN

COUNTRY_CODE
_______________
IE

COUNTRY_CODE
_______________
IE

Unlike UNION, MINUS and EXCEPT depend on order: swapping the two queries asks a different question.

INTERSECT ALL and EXCEPT ALL

The ALL versions keep duplicates and count them. EXCEPT ALL removes one occurrence from the first result for each occurrence in the second, so three A values minus one A leaves two. INTERSECT ALL keeps each value as many times as it occurs in both, so two A values and three A values share two.

Example:

select column_value as code from table(sys.odcivarchar2list('A', 'A', 'A', 'B'))
except all
select column_value from table(sys.odcivarchar2list('A', 'B'));

select column_value as code from table(sys.odcivarchar2list('A', 'A', 'B'))
intersect all
select column_value from table(sys.odcivarchar2list('A', 'A', 'A'));

Output:

CODE
_______
A
A

CODE
_______
A
A

sys.odcivarchar2list is a built-in collection type, used here to make small lists without creating tables. MINUS ALL is a synonym of EXCEPT ALL.

Things to Know

• Set operators treat two NULLs as equal, unlike the = operator, so a NULL row in both queries counts as a match for INTERSECT and MINUS.
• UNION, INTERSECT, MINUS, and EXCEPT cannot be used on LOB columns such as CLOB, because duplicates cannot be found for them. UNION ALL can.
• Each query can have its own WHERE, GROUP BY, and joins; only the final ORDER BY belongs to the whole statement.

Related Guides

• How to Use Subqueries in Oracle SQL
• Oracle SQL Query to Use Full Outer Join for Data Reconciliation

Conclusion

Use UNION ALL to stack results, UNION to stack them without duplicates, INTERSECT for rows common to both, and MINUS or EXCEPT for rows only in the first. Match the column count and types, name the columns in the first query, sort once at the end, and use the ALL versions when duplicates must be counted rather than removed.