SQL SET Operator
In this tutorial, we will understand the operator who falls under the SET operator in SQL with the help of different examples.
The SQL SET Operator is used to merge the output of two or more than two SELECT queries.
The operator falls under the SET operator category is as follows:
- SQL UNION Operator
- SQL UNION ALL Operator
- SQL INTERSECT Operator
- SQL MINUS Operator
There are some rules and regulations to be followed to execute queries using the SET Operator in SQL. Rules are as follows:
- There should be equal numbers of columns, and the order of columns must be the same between both the tables.
- Data Types must be compatible.
Let's learn the SQL SET Operator in detail with the help of an example.
Consider the already existing table with the following data
Table Number 1: Student
Student_Id | Student_Name | City | Age |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
5 | Sakshi Patil | Aurangabad | 22 |
6 | Shruti Sharma | Mumbai | 21 |
7 | Pranoti Shende | Aurangabad | 23 |
8 | Harshada Dhanwat | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
10 | Nikhil Patil | Pune | 24 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
13 | Bhushan Pachpande | Bangalore | 25 |
14 | John Chaudhary | Bangalore | 24 |
15 | Sonakshi Sen | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
Table Number 2: Students
Student_Id | Student_Name | City | Age |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
5 | Swati Sharma | Pune | 22 |
6 | Nikita Ingale | Mumbai | 21 |
7 | Pranoti Shende | Aurangabad | 23 |
8 | Harshada Kakade | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
10 | Naman Sharma | Aurangabad | 24 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
13 | Bhavesh Jain | Bangalore | 25 |
14 | Jayesh Nikam | Bangalore | 24 |
15 | Sonalika Shinde | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
1. SQL UNION Operator
- SQL UNION Operator is used to join or combine the two or more than two SELECT queries.
- The common records are not considered in the output result obtained after the UNION operator is executed.
The syntax for SQL UNION Operator is as follows:
SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 UNION SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;
Example: Write a query to execute UNION operation between student table and students table.
SELECT * FROM Student UNION SELECT * FROM Students;
In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a UNION operation with the data retrieved by the second SELECT statement from the Students table.
The output of the above query is as follows:
Student_Id | Student_Name | City | Age |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
5 | Sakshi Patil | Aurangabad | 22 |
6 | Shruti Sharma | Mumbai | 21 |
7 | Pranoti Shende | Aurangabad | 23 |
8 | Harshada Dhanwat | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
10 | Nikhil Patil | Pune | 24 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
13 | Bhushan Pachpande | Bangalore | 25 |
14 | John Chaudhary | Bangalore | 24 |
15 | Sonakshi Sen | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
5 | Swati Sharma | Pune | 22 |
6 | Nikita Ingale | Mumbai | 21 |
8 | Harshada Kakade | Nashik | 24 |
10 | Naman Sharma | Aurangabad | 24 |
13 | Bhavesh Jain | Bangalore | 25 |
14 | Jayesh Nikam | Bangalore | 24 |
15 | Sonalika Shinde | Bangalore | 25 |
2. SQL UNION ALL Operatorss
- SQL UNION ALL Operator merges all the records from the SELECT statement used in the query.
- Common data are not displayed in the output in the SQL UNION operator. Still, SQL UNION ALL operator allows common data to be displayed in the output obtained after the UNION ALL operation is executed.
The syntax for SQL UNION ALL Operator is as follows:
SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 UNION ALL SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;
Example 1: Write a query to execute UNION ALL operation between student table and student’s table.
SELECT * FROM Student UNION ALL SELECT * FROM Students;
In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a UNION ALL operation with the data retrieved by the second SELECT statement from the Students table.
The output of the above query is as follows:
Student_Id | Student_Name | City | Age |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
5 | Sakshi Patil | Aurangabad | 22 |
6 | Shruti Sharma | Mumbai | 21 |
7 | Pranoti Shende | Aurangabad | 23 |
8 | Harshada Dhanwat | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
10 | Nikhil Patil | Pune | 24 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
13 | Bhushan Pachpande | Bangalore | 25 |
14 | John Chaudhary | Bangalore | 24 |
15 | Sonakshi Sen | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
5 | Swati Sharma | Pune | 22 |
6 | Nikita Ingale | Mumbai | 21 |
7 | Pranoti Shende | Aurangabad | 23 |
8 | Harshada Kakade | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
10 | Naman Sharma | Aurangabad | 24 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
13 | Bhavesh Jain | Bangalore | 25 |
14 | Jayesh Nikam | Bangalore | 24 |
15 | Sonalika Shinde | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
Example 2: Write a query to execute UNION ALL operation between student table and students tables. Display only those records from the Student table where the student resides in the 'Bangalore', 'Hyderabad', and 'Nashik’ cities.
SELECT * FROM Student WHERE City IN ('Nashik', 'Bangalore', 'Hyderabad') UNION ALL SELECT * FROM Students;
In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table where the student resides in the 'Nashik’, 'Bangalore', and 'Hyderabad' cities. It executes a UNION ALL operation with the data retrieved by the second SELECT statement from the Students table.
The output of the above query is as follows:
Student_Id | Student_Name | City | Age |
3 | Sourabh Chougale | Nashik | 23 |
8 | Harshada Dhanwat | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
13 | Bhushan Pachpande | Bangalore | 25 |
14 | John Chaudhary | Bangalore | 24 |
15 | Sonakshi Sen | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
5 | Swati Sharma | Pune | 22 |
6 | Nikita Ingale | Mumbai | 21 |
7 | Pranoti Shende | Aurangabad | 23 |
8 | Harshada Kakade | Nashik | 24 |
9 | Tejas Bairagi | Nashik | 21 |
10 | Naman Sharma | Aurangabad | 24 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
13 | Bhavesh Jain | Bangalore | 25 |
14 | Jayesh Nikam | Bangalore | 24 |
15 | Sonalika Shinde | Bangalore | 25 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
3. SQL INTERSECTS Operators
SQL Intersect operator is used to merge two or more than two SELECT queries, but INTERSECT Operator displays only those common data between both the tables.
The syntax for SQL INTERSECT Operator is as follows:
SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 INTERSECT SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;
Example: Write a query to execute INTERSECTS operation between student table and students table.
SELECT * FROM Student INTERSECT SELECT * FROM Students;
In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a INTERSECTS operation with the data retrieved by the second SELECT statement from the Students table.
The output of the above query is as follows:
Student_Id | Student_Name | City | Age |
1 | Pratik Srivastav | Pune | 23 |
2 | Utkarsh Rokade | Mumbai | 22 |
3 | Sourabh Chougale | Nashik | 23 |
4 | Prateek Zimbre | Pune | 24 |
7 | Pranoti Shende | Aurangabad | 23 |
9 | Tejas Bairagi | Nashik | 21 |
11 | Samaira Sharma | Mumbai | 22 |
12 | Anushka Sen | Aurangabad | 23 |
16 | Mayuri Wagh | Hyderabad | 22 |
17 | Ritika Patil | Hyderabad | 21 |
18 | Tushar Mahale | Hyderabad | 23 |
4. SQL MINUS Operators:
- SQL MINUS operator displays the rows available in the first SELECT query that are not available in the second SELECT query.
- MINUS keyword doesn't work in SQL query. Instead of the MINUS keyword, use EXCEPT keyword to execute the MINUS operator in the SQL.
The syntax for SQL MINUS Operator is as follows:
SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_1 EXCEPT SELECT Column_Name_1, Column_Name_2, Column_Name_3, Column_Name_4 FROM Table_Name_2;
Example: Write a query to execute MINUS operation between student table and students table.
SELECT * FROM Student EXCEPT SELECT * FROM Students;
In the above query, we have executed two SELECT statements. The first SELECT statement displays the data from the Student table and executes a MINUS operation with the data retrieved by the second SELECT statement from the Students table.
The output of the above query is given below:
Student_Id | Student_Name | City | Age |
5 | Sakshi Patil | Aurangabad | 22 |
6 | Shruti Sharma | Mumbai | 21 |
8 | Harshada Dhanwat | Nashik | 24 |
10 | Nikhil Patil | Pune | 24 |
13 | Bhushan Pachpande | Bangalore | 25 |
14 | John Chaudhary | Bangalore | 24 |
15 | Sonakshi Sen | Bangalore | 25 |