Set Operations in SQL

SQL set operators are conceptually derived from mathematical set theory. Set operations are used for combining the result of two or more queries to form a single result set. Queries containing set operators are called as compound queries.

Syntax:

SELECT column_name FROM table_name1

set_operator

SELECT column_name FROM table_name2;

 

To perform set operation on two or more tables, the below conditions must be satisfied.

  • The number of columns and their respective datatype must be same in each table on which set operation is to be performed.
  • The columns must be in same order in each table.

 

There are 3 different types of SET operations in SQL. They are:

1. UNION Operation

UNION operator is used to combine the resultset of multiple SELECT statements by eliminating duplicate rows from the result set table following few conditions.

Example:

Table1   

          id

          age

          1

         23

          2

         33

          3

         26

 

Table2

          id

          age

          2

         33

          3

         26

          4

         45

 

Query:

SELECT * FROM Table1

UNION

SELECT * FROM Table2;

 

Result set table:

          id

         age

          1

         23

          2

         33

          3

         26

          4

         45

 

UNION ALL - It combines the result sets of two or more SELECT statements. It returns duplicate values as well.

Example:

Table1        

          id

         age

          1

         23

          2

         33

          3

         26

 

Table2

          id

         age

          2

         33

          3

         26

          4

         45

Query:

SELECT * FROM Table1

UNION ALL

SELECT * FROM Table2

 

Result set table:

          id

         age

          1

         23

          2

         33

          2

         33

          3

         26

          3

         26

 

          4

         45

Note: Set operators eliminate duplicate values by default. To return duplicate values as well, we must use ALL after the operators such as UNION ALL etc.

 

2. INTERSECT Operation

The INTERSECT operation combines two or more SELECT statements to return the values which are common on each table. Here, the number of columns and their datatypes must be same in each table. And, the result data is arranged in ascending order by default.

Example:

Table1

          id

         age

          1

         23

          2

         33

          3

         26

 

Table2

          id

         age

          2

         33

          3

         26

          4

         45

 

Query:

SELECT * FROM Table1

INTERSECT

SELECT * FROM Table2;

 

Result set table:

          id

         age

          2

         33

          3

         26


Note: The INTERSECT operator is not supported in MySQL. Instead, we can use the IN and EXIST IN clauses for performing the operation.

 

3. MINUS Operation

The MINUS operation combines the result of two SELECT statements to returns only those values which are present only in the result of first SELECT statement. It arranges the result set data in ascending order by default.

Example:

Table1

          id

         age

          1

         23

          2

         33

          3

         26

 

Table2

          id

         age

          2

         33

          3

         26

          4

         45

 

Query:

SELECT * FROM Table1

MINUS

SELECT * FROM Table2;

 

Result set table:

          id

          age

          1

          23


Note: The MINUS operator is supported only in Oracle databases. To perform it in other databases such as SQLite, SQL Server etc. make use of EXCEPT operator in place of MINUS.