Table of contents
1.
Introduction
2.
What are Set Operators in SQL?
2.1.
Syntax of Set Operators in SQL
2.2.
SQL
2.3.
SQL
2.4.
SQL
2.5.
Parameters of Set Operators in SQL
3.
Types of Set Operators in SQL
3.1.
UNION Operator
3.2.
SQL
3.3.
SQL
3.4.
UNION ALL Operator
3.5.
SQL
3.6.
SQL
3.7.
INTERSECT Operator
3.8.
SQL
3.9.
SQL
3.10.
MINUS/EXCEPT Operator
3.11.
SQL
3.12.
SQL
4.
How to Use SET Operators in SQL?
5.
Exceptions in Set Operators in SQL
6.
Frequently Asked Questions
6.1.
What is the set operator in SQL?
6.2.
Which are some of the frequently used set operators in SQL?
6.3.
Which databases support the MINUS operator in SQL?
6.4.
What is the major difference between the UNION and UNION ALL operators in SQL?
7.
Conclusion
Last Updated: Mar 27, 2024
Easy

Set Operators in SQL

Author Vasu Bansal
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

In real life, many situations require us to merge results from two different outputs into one result. SQL or Structured Query Language helps in managing relational databases. With the help of SQL, users can query, modify and perform updates to databases very easily. The set operators in SQL are used to combine results from two or more SQL queries into one single result. Precisely, it helps combine results from two SELECT queries that return the same data format. 

Set operators in SQL

In this article, you will explore the different set operators in SQL  to combine two different outputs into one output and understand them with the help of examples. Without further ado, let’s get started!

What are Set Operators in SQL?

The set operators in SQL are used to combine results from two SELECT statements into a single output. The tables that are being combined must return the same columns and data type so that they can be combined using the set operators. If this condition is not satisfied, it will lead to a SQL exception. The set operators provided by SQL include:

  1. UNION
  2. UNION ALL
  3. INTERSECT
  4. MINUS/EXCEPT

Syntax of Set Operators in SQL

Set operators in SQL allow you to combine and compare the results of multiple queries. The basic set operators are UNION, INTERSECT, and EXCEPT (or MINUS). The generic syntax for working with SQL set operators is as follows:

  • UNION merges the results of two or more SELECT queries into a single result set, removing duplicates.
     
  • SQL

SQL

mysql> SELECT *FROM Table_1 UNION SELECT *FROM Table_2;  

 

  • INTERSECT returns only the standard rows between two SELECT queries.
     
  • SQL

SQL

mysql> SELECT *FROM Table_1 INTERSECT SELECT *FROM Table_2;  

 

  • EXCEPT (or MINUS) returns the rows from the first query that do not appear in the second query.
     
  • SQL

SQL

mysql> SELECT *
 FROM Table_1

 EXCEPT 
SELECT *
 FROM Table_2


The syntax typically involves using these operators between the SELECT statements. For example, "SELECT column1 FROM table1 UNION SELECT column1 FROM table2;" would combine the results of two queries into one result set.

Also read, Natural Join in SQL

Parameters of Set Operators in SQL

In SQL, set operators combine the results of two or more SELECT queries. There are three main set operators: UNION, INTERSECT, and EXCEPT (or MINUS in some databases).

1. UNION: Combines and returns distinct rows from multiple queries into a single result set, removing duplicates.

2. INTERSECT: Returns only the common rows that appear in both query results.

3. EXCEPT (MINUS): Retrieves rows that exist in the first query but not in the second query's result.

Set operators help to efficiently merge and compare data from different tables or queries, allowing users to perform powerful operations to analyze and manipulate data effectively.

Types of Set Operators in SQL

In this section, we will discuss the various set operators in SQL. Consider that we have the following two tables with the given sample data in our database. We will use these tables to understand all the set operators in SQL. The first table is called “Accounts”, which stores the employee details of the accounts department. The second is called “Sales”, which stores the employee details of the sales department. The data in both tables is as follows:

Accounts:

Emp_id

Name

Working_status

1

Saurabh

WFH

2

Akash

Hybrid

3

Ayush

Office

4

Vaishnavi

WFH

5

Neha

Office

Sales:

Emp_id

Name

Working_status

6

Vikas

WFH

2

Akash

Hybrid

15

Vasu

Office

14

Pradeep

WFH

21

Vivek

Office

UNION Operator

The UNION set operators in SQL are used to combine the results of two or more SQL SELECT queries. It is important to note that no duplicate entries are present in the result obtained from UNION. This point will later be highlighted again with the help of an example.

Union operation

Syntax:
 

  • SQL

SQL

SELECT col1, col2, .. , coln
FROM firstTable
UNION
SELECT col1, col2, …, coln
FROM secondTable;

 

Example:

Suppose we want to get a combined list of all the employees that work in the Accounts and Sales department. We can write the following SQL query to obtain this list.

Code:
 

  • SQL

SQL

SELECT * FROM Accounts
UNION
SELECT * FROM Sales;


Output:

Emp_id

Name

Working_status

1

Saurabh

WFH

2

Akash

Hybrid

3

Ayush

Office

4

Vaishnavi

WFH

5

Neha

Office

6

Vikas

WFH

15

Vasu

Office

14

Pradeep

WFH

21

Vivek

Office

Note: The employee named Akash, with employee id 2, works for both the Accounts and Sales departments. But the UNION operator query output returns only one entry of this employee, as the UNION operator automatically removes duplicate entries.

UNION ALL Operator

The UNION ALL set operators in SQL function similarly to the UNION set operators in SQL. Thus, the UNION ALL operator is also used to combine results from two or more SELECT queries. The only difference between the UNION and UNION ALL operators is that the UNION ALL does not remove duplicate elements, unlike the UNION operator.

UNION ALL

Syntax:
 

  • SQL

SQL

SELECT col1, col2, .. , coln
FROM firstTable
UNION ALL
SELECT col1, col2, …, coln
FROM secondTable;

 

Example:

The following query performs the UNION ALL operation on the Accounts and Sales tables.

Code:
 

  • SQL

SQL

SELECT * FROM Accounts
UNION ALL
SELECT * FROM Sales;


Output:

Emp_id

Name

Working_status

1

Saurabh

WFH

2

Akash

Hybrid

3

Ayush

Office

4

Vaishnavi

WFH

5

Neha

Office

6

Vikas

WFH

2

Akash

Hybrid

15

Vasu

Office

14

Pradeep

WFH

21

Vivek

Office

Note: The employee named Akash, with employee id 2, works for both the Accounts and Sales departmentsHence, this employee's entry is present twice in the output. This is because the UNION ALL operator does not remove duplicate entries, unlike the UNION operator.

INTERSECT Operator

The INTERSECT set operators in SQL are also used to combine the results of two SELECT queries, but the combination procedure is different. The INTERSECT operator returns only those records which are common between both the outputs that are being combined.

INTERSECT

Syntax:

  • SQL

SQL

SELECT col1, col2, .. , coln
FROM firstTable
INTERSECT
SELECT col1, col2, …, coln
FROM secondTable;

 

Example:

The following query performs the INTERSECT operation on the Accounts and Sales tables.

Code:
 

  • SQL

SQL

SELECT * FROM Accounts
INTERSECT
SELECT * FROM Sales;


Output:

Emp_id

Name

Working_status

2

Akash

Hybrid

Note: Only the employee named Akash with employee id 2 works for both the Accounts and Sales departmentsHence, the INTERSECT set operator returns only this record in the resultant output.

MINUS/EXCEPT Operator

The MINUS/EXCEPT operator returns the list of records that are present in the first table and not in the second table. Databases like SQLite, SQL Server, and PostgreSQL support the EXCEPT set operators in SQL. The MINUS operator is supported only by Oracle databases. 

MINUS/EXCEPT

Syntax:
 

  • SQL

SQL

SELECT col1, col2, .. , coln
FROM firstTable
EXCEPT
SELECT col1, col2, …, coln
FROM secondTable;

 

Example:

Suppose we want the list of employees working for the Accounts department, not the Sales department. We can use the following SQL query to achieve this task:

Code:
 

  • SQL

SQL

SELECT * FROM Accounts
EXCEPT
SELECT * FROM Sales;


Output:

Emp_id

Name

Working_status

1

Saurabh

WFH

3

Ayush

Office

4

Vaishnavi

WFH

5

Neha

Office

The output contains all the employees working for the Accounts department, not the Sales department.

How to Use SET Operators in SQL?

The following are the rules for using set operators in SQL. Following these rules ensure that the set operators provide the desired results without leading to the exceptions discussed at the end of the article.

  1. All the SELECT statements used in the query should return the same number of columns. Also, the data types of the columns should match for the set operators to work.
     
  2. We can apply the ORDER BY only to the resultant set, i.e., we cannot use the ORDER BY clause with the individual SELECT queries.
     
  3. The UNION operation removes duplicate records from the resultant set, whereas the UNION ALL operation retains the duplicate records.
     
  4. The INTERSECT operation returns the common records from the two SELECT queries.
     
  5. The MINUS/EXCEPT operation returns the records from the first table which are not present in the second.

Exceptions in Set Operators in SQL

The following are some of the exceptions that may arise while using set operators in SQL:

  • Column Mismatch: An exception will occur if the number of columns or their data types differs for both queries.
     
  • Type Casting: If the data type of the columns does not match, SQL will try to convert the data type of the columns as per the standard type casting rules.
     
  • NULL value exception:  A NULL value cannot be compared with another NULL value. Thus having NULL values when using MINUS/EXCEPT set operation can lead to exceptions.

Frequently Asked Questions

What is the set operator in SQL?

In SQL, set operators combine the results of multiple queries. Common set operators include UNION (combines distinct rows from multiple queries), INTERSECT (returns common rows between queries), and EXCEPT (returns rows from the first query that are not in the second query).

Which are some of the frequently used set operators in SQL?

Some of the frequently used set operators in SQL include UNION, UNION ALL, EXCEPT, INTERSECT, etc. Remember that the UNION operator removes the duplicate records from the resultant output, whereas all duplicate records are retained in the UNION ALL output set.

Which databases support the MINUS operator in SQL?

Oracle databases support the MINUS operator in SQL. Databases such as MySQL, SQL Server and PostgreSQL support the EXCEPT operator in SQL. Both the EXCEPT and MINUS operators are used to find the records which are present in the first table but not in the second. 

What is the major difference between the UNION and UNION ALL operators in SQL?

The UNION set operators in SQL remove all the duplicate records from the resultant output (if any). On the other hand, the UNION ALL set operator keeps all the duplicate records in the resultant output.

Conclusion

Congratulations on finishing the blog! We have discussed the set operators in SQL. The UNION and UNION ALL operators combine results from two different SELECT queries, INTERSECT finds the common records and EXCEPT finds those records which are present in the first table but not in the second.

We hope this blog has helped you enhance your knowledge. Do not stop learning! We recommend you read some of our SQL articles: 

1.SQL Expressions

2. SQL Syntax

3. WHERE Clause in SQL

4. LIMIT in SQL

Happy Learning!

Live masterclass