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:

1. SQL UNION Operator
2.  SQL UNION ALL Operator
3.  SQL INTERSECT Operator
4.  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:

1.  There should be equal numbers of columns, and the order of columns must be the same between both the tables.
2.  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