Set Operations

Set Operations

The set operations that SQL allows to be performed on a table data in a database are union, intersect and except. These set operations operate on relations and corresponds to the relational algebra operations ∪,∩ and −.

Each of these operations automatically eliminates duplicates. In order to retain all duplicates, we need to write union all, intersect all and except all respectively.

Union is used to combine the results of two or more Select statements. However, it will eliminate duplicate rows from its result set. In the case of a union, a number of columns and data type must be same in both the tables. For example:

The First table

The Second table

Here the union query will be,

select * from First

UNION

select * from Second

Hence, the resulting table is,

Union all

This operation is similar to Union. But it also shows the duplicate rows. For example:

The First table

The Second table

Here the union all query will be,

select * from First

UNION ALL

select * from Second

The resulting table is,

Intersect operation is used to combine two SELECT statements, but it only returns the records which are common from both SELECT statements. In a case of Intersect, the number of columns and data type must be same. For example:

The First table

The Second table

Intersect query will be,

select * from First table

INTERSECT

select * from Second table

The resulting table will be,

Except operators combines the result of two Select statements and return only those result which belongs to the first set of result. For example:

The First table

The Second table

Except query will be,

select * from First

EXCEPT

select * from Second

The resulting table will be,

References

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhatt acharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.