UNION UNION ALL MINUS Set Operators in SQL

For Complete YouTube Video: Click Here

In this class, we will understand UNION UNION ALL MINUS Set Operators in SQL.

We have already discussed the concepts of INTERSECT operator in SQL.

UNION UNION ALL MINUS Set Operators in SQL

UNION UNION ALL MINUS are all Set Operators in SQL.

Before understanding the use of UNION UNION ALL MINUS in SQL, we will understand the mathematical approach of these concepts.

For example, consider sets A and B as shown below.

A = {1, 2, 3, 4}, B = {3, 4, 5, 6}.

The UNION of two sets will have unique elements of both ‘sets.’

A UNION B = {1, 2, 3, 4, 5, 6}.

The UNION ALL of the two sets will have all elements of both ‘sets.’

A UNION ALL B = {1, 2, 3, 4, 3, 4, 5, 6}.

The MINUS of two sets will have elements of B subtracted from the ‘elements’ of A.

A MINUS B = {1, 2}.

Use of UNION UNION ALL MINUS

The UNION operator is used to combine the result-set of two or more SELECT statements.

The rules to use union operator.

1. Every SELECT statement within UNION must have the same number of columns

2. The columns must also have similar data types

3. The columns in every SELECT statement must also be in the same order

MINUS returns those tuples that are returned by the first SELECT operation, and not returned by the second SELECT operation.

To understand this, we consider the tables as shown below.

The query and the output of the ‘query’ using UNION are as shown below.

The query and the output of the ‘query’ using UNION ALL are as shown below.

The query and the output of the ‘query’ using MINUS are as shown below.