Set Operators

The Oracle set operators

All set operators make compound queries by combining the result sets from two or more queries

• UNION

             Returns the combined rows from two queries, sorting them and removing duplicates.

• UNION ALL

             Returns the combined rows from two queries without sorting or removing duplicates.

• INTERSECT

             Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.

• MINUS

             Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

Examples:

• Creatures with two legs Humans, parrots, bats
• Creatures that can fly Parrots, bats, bees
• Creatures with fur Bears, bats

The union of the three sets is humans, parrots, bats, bees, and bears. This is all the elements in all the sets, without the duplications.

The intersection of the sets is all elements that are common to all three sets, again removing the duplicates. In this simple example, the intersection has just one element: bats. The intersection of the two-legged set and the flying set has two elements: parrots and bats.

The minus of the sets is the elements of one set without the elements of another, so the two-legged creatures set minus the flying creatures set results in a single element: humans.