Set Operators in SQL

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:

Sales:

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.

Syntax:
 

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:
 

SELECT * FROM Accounts
UNION
SELECT * FROM Sales;

Output:

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.

Syntax:
 

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:
 

SELECT * FROM Accounts
UNION ALL
SELECT * FROM Sales;

Output:

Note: The employee named Akash, with employee id 2, works for both the Accounts and Sales departments. Hence, 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.

Syntax:

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:
 

SELECT * FROM Accounts
INTERSECT
SELECT * FROM Sales;

Output:

Note: Only the employee named Akash with employee id 2 works for both the Accounts and Sales departments. Hence, 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. 

Syntax:
 

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:
 

SELECT * FROM Accounts
EXCEPT
SELECT * FROM Sales;

Output:

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!