SQL Set Operations

Read this article to understand the Set Operations along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of SQL Set Operations with all its options. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of Set Operations and why we should use it.

Definition

The Set operations are used to combine the result of multiple select statement. Sometimes we want to combine the result of multiple tables where we have common columns. Set having four common operations that we can use to combine the result while some operations are used to skip the result. Set operators can be used with Select command.

Reasons to Use

Set operations are used to combine the result of multiple select statement. Below are the some key areas where we need to use Set operators.

1. Set operations allow you to retrieve data from multiple tables and combine it into a single result set.
2. Set operations can be used to filter and sort data based on specific criteria.
3. Set operations allow you to remove duplicates from the result set, ensuring that each row is unique.
4. Set operations can also be used to perform calculations on the data in the result set.

Below are the some set operators. Some DBMS engine supports all while some doesn’t supports.

Union

UNION operator combines the result of two or more SQL select statements. It combines the result into single result set and remove the duplicates records. Union will always return distinct rows based on all columns.

Syntax

SELECT column1, column2, columnN 
FROM table_name1
[WHERE condition]
UNION
SELECT column1, column2, columnN 
FROM table_name2
[WHERE condition];;

Union operations needs the number of columns should be same and exact matching from each select statement.

Example

Example 1:

We have “Customers” table, having 5 records with few columns. While we have second “Employees” tables having 4 records. Lets retrieve the unique names from both the tables.

Above example will combine the result from both tables and skip the duplicate records from final result set.

Union All

UNION ALL operator combines the result of two or more SQL select statements. It combines the result into single result set and keep the duplicates records as it is. Union All will return duplicate rows also.

Syntax

SELECT column1, column2, columnN 
FROM table_name1
[WHERE condition]
UNION ALL
SELECT column1, column2, columnN 
FROM table_name2
[WHERE condition];;

Union ALL operations also needs the number of columns should be same and exact matching from each select statement.

Example

Example 2:

We have “Customers” table, having 5 records with few columns. While we have second “Employees” tables having 4 records and 1 records will be duplicate. Lets retrieve the all names from both the tables.

Above example will combine the result from both tables and include the duplicate records into final result set.

Intersect

The INTERSECT operator returns the common rows between the results of two SELECT statements. However, not all database management systems natively support this operator. Here is an syntax of how to use it in SQL.

Syntax

SELECT column1, column2, columnN 
FROM table_name1
[WHERE condition]
INTERSECT
SELECT column1, column2, columnN 
FROM table_name2
[WHERE condition];;

Intersect operations needs the number of columns should be same and exact matching from each select statement.

Example

Example 1:

We have “Customers” table, having 5 records with few columns. While we have second “Employees” tables having 4 records. Lets retrieve the common names from both the tables.

Above example will combine the result from both tables and show only the common records into final result set.

EXCEPT or MINUS

The EXCEPT (or MINUS) operator returns the rows in the first SELECT statement that are not in the second SELECT statement. However, not all database management systems natively support this operator. Here is an syntax of how to use it in SQL.

Syntax

SELECT column1, column2, columnN 
FROM table_name1
[WHERE condition]
EXCEPT
SELECT column1, column2, columnN 
FROM table_name2
[WHERE condition];;

Except operations needs the number of columns should be same and exact matching from each select statement.

Example

Example 1:

We have “Customers” table, having 5 records with few columns. While we have second “Employees” tables having 4 records. Lets execute MINUS or EXCEPT operations on this data.

customer_id	first_name	last_name	age	country
1	John	Doe	31	USA
2	Robert	Luna	22	USA
3	David	Robinson	32	UK
5	James	Doe	19	INDIA

Above example shows the except operation. MySQL doesn’t support for INTERSECT, EXCEPT or MINUS operations. So we need to implement it using join and distinct keyword.

I hope all above example clears the concept of Set operations and it’s use.

Linking

Set Operators are used with Select statement. There are four set operators like UNION, UNION ALL, INTERSECT, EXCEPT or MINUS. We can use Where clause in both select statement. You can refer all the interview questions. Please refer all the links for understanding the SET Operators in depth.

SQL Set Operations with Latest SQL Interview Questions 2023

Basic Set Operations SQL Interview questions

1. What are SQL set operations, and why are they important?
2. What is the difference between the UNION and UNION ALL operators?
3. What is the difference between the INTERSECT and EXCEPT operators?

Intermediate Set Operations SQL Interview questions

1. How do you ensure that the result set of a set operation is ordered in a specific way?
2. Can you perform a set operation between tables with different column names?
3. How do you use the GROUP BY clause in conjunction with set operations?

Advanced Set Operations SQL Interview questions

1. How do you use the INTERSECT and EXCEPT operators in MySQL, since they are not natively supported?
2. What is the order of precedence for set operations in SQL?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : How do you use the INTERSECT and EXCEPT operators in MySQL, since they are not natively supported?

Ans : The INTERSECT operations returns only the common rows from both the table. MySQL doesn’t support for INTERSECT operations but we can implement it using INNER JOIN. lets see the below syntax.

SELECT *
FROM table1
INNER JOIN table2
ON table1.col1 = table2.col1
AND table1.col2 = table2.col2;

The EXCEPT (or MINUS) operator returns the rows in the first SELECT statement that are not in the second SELECT statement. MySQL doesn’t support for MINUS operator but we can implement it using LEFT JOIN. lets see the below syntax.

SELECT *
FROM table1
LEFT JOIN table2
ON table1.col1 = table2.col1
AND table1.col2 = table2.col2
WHERE table2.col1 IS NULL;

INTERSECT and EXCEPT/MINUS can be implement using inner query with EXIST or NOT EXIST keyword.

Quiz

Click on this link to solve few MCQ questions on Set Operations.

I hope you have enjoyed this session. Happy offline learning… Lets meet in next session.