Set Operations

We can perform the following set operations in SQL:

• union

• intersect

• except (for difference)

 select * from r union/intersect/except select * from s

Each of these operations automatically eliminates duplicates. To retain duplicates, we must use union all, intersect all and except all.

Suppose a tuple occurs m times in r and n times in s, then, it occurs:

• m + n times in r union all s

• min(m,n) times in r intersect all s

• max(0, m–n) times in r except all s