How to Use the Set Operations in SQL ?

SQL (Structured Query Language) provides set operators that are performed on data that is in tables. Using set operators we can combine data from 2 or more tables/queries to get the final result after performing a particular Set Operation. Set Operations in SQL to combine data that is of different types from 2 or more tables.

Scope of the Article

• In this article,  we will discuss how to use the Set Operations in SQL.
• We will see what are the different types of Set Operators in SQL.
• Set Operators we will discuss are UNION, UNION ALL, INTERSECT, and MINUS.
• We will understand these with examples for clear understanding.

Set Operations in SQL :

→ People often misunderstand set operators as opposite to offset in SQL. But it is not, set operators combine data that is of type from 2 or more tables.

→ SQL Set Operations are a bit similar to Joins in SQL as both are used to combine data from 2 or more tables.

→ But there is a huge difference between Set Operations in SQL and Joins in SQL

→ Joins in SQL are used to combine data from 2 or more tables by columns whereas Set Operations in SQL are used to combine data from 2 or more tables by rows.

→ Set Operations in SQL are easier to perform and use than the joins in SQL.

Syntax for Using/Performing Set Operations

SELECT * from table1

SET OPERATION

SELECT * from table2;

Before Knowing about different types of set operations, let us discuss some rules which have to be followed in performing Set Operations.

1. The Order of selecting columns should be in the same order.

2. Selected Columns should have the same data type.

3. For sorting the results we can use the ORDER BY keyword which has to be placed at the end of the list, but not for each select statement.

4. Number of columns taken in select statements must be the same.

Now, let us discuss the different types of Set Operations in SQL,

Different types of Set Operations

The different set operations we have in SQL (Structured Query Language) are,

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

Let us discuss each of these in detail,

1. UNION Operation

As we all know UNION operation combines the data of 2 or more select statements from different tables. All tables taken should have the same columns with the same data type and the number of columns must be the same in select statements we write.

When the UNION operation is performed, duplicated data in the rows will be ignored in the final result.

Syntax:

SELECT exp1, exp2, … , expn

FROM table1

UNION

SELECT exp1, exp2, … , expn

FROM table2

Let us understand in detail with example,

Table 1: Students_1

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Ramana	IT
4	Mary	IT
5	John	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mehul	CST
9	Anush	CSE
10	Neeru	CSE

Table 2: Students_2

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Rajesh	IT
4	Mary	IT
5	Mike	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mohan	CST
9	Anush	CSE
10	Niha	CSE

Query using UNION operation,

SELECT * FROM Students_1

UNION

SELECT FROM Students_2

Output Obtained is,

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Ramana	IT
3	Rajesh	IT
4	Mary	IT
5	John	CSE
5	Mike	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mehul	CST
8	Mohan	CST
9	Anush	CSE
9	Anush	CSE
10	Neeru	CSE
10	Niha	CSE

2. UNION ALL Operation

As we all know UNION operation combines the data of 2 or more select statements from different tables. Similarly, the UNION ALL operation combines the data of 2 or more select statements from different tables  In which all tables taken should have the same columns with the same data type and the number of columns must be the same in select statements we write. Before moving ahead, to get an overall understanding of UNION ALL and SET operations, it is advisable to learn SQL from the basics and first get your foundational knowledge gets strong enough

.

When the UNION ALL operation is performed duplicated data in the rows will not be ignored in the final result. Duplication will not be removed while UNION ALL is performed.

Syntax:

SELECT exp1, exp2, … , expn

FROM table1

UNION ALL

SELECT exp1, exp2, … , expn

FROM table2

Let us understand in detail with example,

Table 1: Students_1

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Ramana	IT
4	Mary	IT
5	John	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mehul	CST
9	Anush	CSE
10	Neeru	CSE

Table 2: Students_2

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Rajesh	IT
4	Mary	IT
5	Mike	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mohan	CST
9	Anush	CSE
10	Niha	CSE

Query using UNION operation,

SELECT * FROM Students_1

UNION ALL

SELECT FROM Students_2

Output Obtained is,

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Ramana	IT
4	Mary	IT
5	John	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mehul	CST
9	Anush	CSE
10	Neeru	CSE
1	Ramu	CSE
2	Raju	ECE
3	Rajesh	IT
4	Mary	IT
5	Mike	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mohan	CST
9	Anush	CSE
10	Niha	CSE

3. INTERSECT Operation

As per our knowledge intersection is something that gives the common data from the data taken. Similarly, INTERSECT in SQL is also responsible to result in the common data existing in the tables selected and as per the Select Statement written. All tables taken should have the same columns with the same data type and the number of columns must be the same in select statements we write.

Syntax:

SELECT exp1, exp2, … , expn

FROM table1

INTERSECT

SELECT exp1, exp2, … , expn

FROM table2

Let us understand in detail with example,

Table 1: Students_1

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Ramana	IT
4	Mary	IT
5	John	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mehul	CST
9	Anush	CSE
10	Neeru	CSE

Table 2: Students_2

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Rajesh	IT
4	Mary	IT
5	Mike	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mohan	CST
9	Anush	CSE
10	Niha	CSE

Query using UNION operation,

SELECT * FROM Students_1

INTERSECT

SELECT FROM Students_2

Output Obtained is,

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
4	Mary	IT
6	Seetha	EEE
7	Kunal	CSD
9	Anush	CSE

4. MINUS Operation

MINUS Operation is allowed to filter results from queries written, which gives the result data present in the 1st query and should not be present in the 2nd query. So, basically returns data from the 1st Query by eliminating data that is common to the 2nd query.  All tables taken should have the same columns with the same data type and the number of columns must be the same in select statements we write.

We can also call a MINUS operation as EXCEPT Operation, which does the same operation.

Syntax:

SELECT exp1, exp2, … , expn

FROM table1

INTERSECT

SELECT exp1, exp2, … , expn

FROM table2

Let us understand in detail with example,

Table 1: Students_1

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Ramana	IT
4	Mary	IT
5	John	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mehul	CST
9	Anush	CSE
10	Neeru	CSE

Table 2: Students_2

Student_Id	Student_Name	Branch
1	Ramu	CSE
2	Raju	ECE
3	Rajesh	IT
4	Mary	IT
5	Mike	CSE
6	Seetha	EEE
7	Kunal	CSD
8	Mohan	CST
9	Anush	CSE
10	Niha	CSE

Query using UNION operation,

SELECT * FROM Students_1

INTERSECT

SELECT FROM Students_2

Output Obtained is,

Student_Id	Student_Name	Branch
3	Ramana	IT
5	John	CSE
8	Mehul	CST
9	Anush	CSE

These are types of Set Operations and the way we use them. We see all these examples for better understanding.

According to the operation mentioned, the query/ SELECT statement executes and gives the output.

Conclusion

1. Set Operators combine data that is of type from 2 or more tables. 
2. SQL Set Operations are a bit similar to Joins in SQL as both are used to combine data from 2 or more tables. But there is a huge difference between Set Operations in SQL and Joins in SQL.
3. Known the Syntax of Set Operations in SQL.
4. Discussed the different types of Set Operations such as UNION, UNION ALL, INTERSECT, MINUS.
5. We discussed each of the types with syntax and examples for better explanation.
6. All Set operations require the number of columns and the datatypes of columns to be the same in select statements we write.
7. UNION operation in SQL combines the data of 2 or more select statements from different tables with the elimination of duplicate rows.
8. UNION ALL operation in SQL combines the data of 2 or more select statements from different tables without the elimination of duplicate rows.
9. INTERSECT Operation in SQL is responsible to result in the common data existing in the tables selected and as per Select Statement.
10. MINUS Operation in SQL returns data from the 1st Query by eliminating data that is common to the 2nd query.