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
- 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.
- Known the Syntax of Set Operations in SQL.
- Discussed the different types of Set Operations such as UNION, UNION ALL, INTERSECT, MINUS.
- We discussed each of the types with syntax and examples for better explanation.
- All Set operations require the number of columns and the datatypes of columns to be the same in select statements we write.
- UNION operation in SQL combines the data of 2 or more select statements from different tables with the elimination of duplicate rows.
- UNION ALL operation in SQL combines the data of 2 or more select statements from different tables without the elimination of duplicate rows.
- INTERSECT Operation in SQL is responsible to result in the common data existing in the tables selected and as per Select Statement.
- MINUS Operation in SQL returns data from the 1st Query by eliminating data that is common to the 2nd query.