Oracle set operators: union, intersect, minus

The most commonly used set operators in Oracle are:

• union
• intersect
• minus

Union Union combine results returned by two or queries into a single table. In a union, all tables must have matching columns. It shows all rows from the first query and all rows from the second query while removing duplicate entries. It has the syntax:

query1 union query2

For example, we have a query1

select name, salary from employee where gender = 'M' order by salary

and a query2

select name, salary from employee where gender = 'F' order by salary

A union would be

select name, salary from employee where gender = 'M'
union
select name, salary from employee where gender = 'F' order by salary

Query1 shows names and salaries of all male employees. Query2 shows names and salaries of all female employees. The union query shows the names and salaries of all male and female employees.

Note that I have removed the order by clause from query1. Queries inside a union cannot be ordered. However, the resulting rows from a union can be ordered.

Intersect Intersect operator takes results from two queries and returns only the rows that appear in both results. Syntax

query1 intersect query2

For example, we have a query1

select name, salary from employee where salary > 100000

and a query2

select name, salary from employee where gender = 'F'

An intersect would be

select name, salary from employee where gender = 'M'
intersect
select name, salary from employee where gender = 'F' order by salary

Query1 shows names and salaries of all employees making over 100k a year. Query2 shows names and salaries of all female employees. The intersect table shows names and salaries of all female employees making over 100k.

Minus Minus operator takes results from two queries and returns only the rows that appear in the results of the first query but not the second. Syntax:

query1 minus query2

For example, we have a query1

select name, salary from employee where salary > 100000

and a query2

select name, salary from employee where gender = 'F'

An intersect would be

select name, salary from employee where gender = 'M'
minus
select name, salary from employee where gender = 'F' order by salary

Query1 shows names and salaries of all employees making over 100k a year. Query2 shows names and salaries of all female employees. The minus table shows names and salaries of all male employees making over 100k.