Home » DBMS SQL Set Operation

DBMS SQL Set Operation

by Online Tutorials Library

SQL Set Operation

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

  1. Union
  2. UnionAll
  3. Intersect
  4. Minus

DBMS SQL Set Operation

1. Union

  • The SQL Union operation is used to combine the result of two or more SQL SELECT queries.
  • In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied.
  • The union operation eliminates the duplicate rows from its resultset.

Syntax

Example:

The First table

ID NAME
1 Jack
2 Harry
3 Jackson

The Second table

ID NAME
3 Jackson
4 Stephan
5 David

Union SQL query will be:

The resultset table will look like:

ID NAME
1 Jack
2 Harry
3 Jackson
4 Stephan
5 David

2. Union All

Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting the data.

Syntax:

Example: Using the above First and Second table.

Union All query will be like:

The resultset table will look like:

ID NAME
1 Jack
2 Harry
3 Jackson
3 Jackson
4 Stephan
5 David

3. Intersect

  • It is used to combine two SELECT statements. The Intersect operation returns the common rows from both the SELECT statements.
  • In the Intersect operation, the number of datatype and columns must be the same.
  • It has no duplicates and it arranges the data in ascending order by default.

Syntax

Example:

Using the above First and Second table.

Intersect query will be:

The resultset table will look like:

ID NAME
3 Jackson

4. Minus

  • It combines the result of two SELECT statements. Minus operator is used to display the rows which are present in the first query but absent in the second query.
  • It has no duplicates and data arranged in ascending order by default.

Syntax:

Example

Using the above First and Second table.

Minus query will be:

The resultset table will look like:

ID NAME
1 Jack
2 Harry

Next TopicDBMS SQL Cursors

You may also like