Set Operators - (UNION, UNION ALL, INTERSECT and MINUS)

There are four set operators in SQL. They are UNION, UNION ALL, INTERSECT and MINUS.

Let us take two tables T1 and T2

T1      T2

----      ---

1         1

2         2

3         3

4         5

1) UNION

Displays data from both tables eliminating duplicate rows.

Example

SELECT A FROM T1

UNION

SELECT A FROM T2;

         A
     --------
         1
         2
         3
         4
         5

2) UNION ALL

Displays data from both tables without eliminating duplicate rows and it is faster.

Example

SELECT A FROM T1

UNION ALL

SELECT A FROM T2;

         A
     --------
         1
         2
         3
         4
         5

3) INTERSECT

Displays data which is commonly present both the table.

Example

SELECT A FROM T1

INTERSECT

SELECT A FROM T2;

         A
    ----------
         1
         2
         3

4) MINUS

Displays data from Table 1 which is not present in Table 2.

Example

SELECT A FROM T1

MINUS

SELECT A FROM T2;

        A
      -------
         4

Rules

•  It works from top to bottom.

•  Both column data type should be same.

• Number of columns in both the query must be same.