UNION vs UNION ALL and INTERSECT vs EXCEPT SET Operators in SQL

In the interview UNION vs UNION ALL is the most frequent question. One more related question with this topic is INTERSECT vs EXCEPT as well. In this article, we will walk through all these questions along with the alternative options for them as well.

Please visit our Youtube Channel for more SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Today's Interview Questions

Q01. What is the difference between UNION vs UNION ALL in SQL?

Q02. What is the difference between INTERSECT vs EXCEPT in SQL?

Q03. What is the difference between UNION vs JOIN in SQL?

Q04. What is the difference between EXCEPT vs MINUS in SQL?

Q05. What is the difference between UNION vs IN Operator in SQL?

Q06. What is the difference between INTERSECT vs IN Clause in SQL?

Q07. What is the difference between EXCEPT vs NOT IN Clause in SQL?

Q08. What is the difference between INTERSECT vs JOIN in SQL?

Q09. What is the difference between EXCEPT vs LEFT JOIN in SQL?

Q10. What are the different SET Operators in SQL?

Introduction

I have two tables, Employee and EmployeeArchive, which contain the employee records. Both the table have the same data type and structure for the columns. The EmployeeArchive has one additional column ArchivedOn and both the table has Id as Primary Key.

For this demonstration, I have kept both the table and data very short and simple. We will use these tables for all the demos here in this article.

UNION vs UNION ALL in SQL

Both UNION and UNION ALL are used to combine the result-set of two SELECT queries into a single result-set. One should have to follow the following rule while using UNION operations:

The Number of Columns must be the same in both the SELECT statements.
The Order of Columns must be the same in both the SELECT statements.
The Type of Columns must be the same in both the SELECT statements.

Apart from this if you want to sort the result set, you must have to use the ORDER BY clause after the last SELECT Query statement, otherwise, you will get a syntax error.

In the UNION operation, the order of SELECT Statements does not matter. So if you change the SELECT statement order, you will still get the same result.

UNION ALL SET Operator

The UNION ALL combine the result of both the SELECT statements without any sorting and removing the duplicate.

So UNION ALL does not remove the duplicate records from the final resultset. All the duplicate rows will be there in the result if any.

If you see the final result, it contains the duplicate records for Alex and Peter.

Since the UNION ALL Operator does not remove the duplicate rows, it is faster compared to the UNION Operator. If you see the Query Plan, it is just doing concatenation only, nothing else.

UNION SET Operator

The UNION operator also combines the result of both the SELECT statements but it removes the duplicate records if any. The order of the SELECT statements does not matter in this case as well.

If you see the final result, it does not contain any duplicate records.

Since the UNION Operator removes the duplicate rows, it is slower compared to the UNION ALL Operator.

If you see the Query Plan, it is doing Distinct Sorting for duplicate removal after the concatenation, and in SQL sorting is the most expensive operation.

UNION vs JOIN

This is a common interview question because both are used to combine or merge the results.

There is no link between them. Both are used for doing different aspects.

UNION operator is used for merging the rows of two tables or two select statements. On the other hand, the JOIN is used to merge the columns of two tables or select statements.

There is no restriction for using JOIN like UNION operator ie. number, order, and type of columns.

Please visit here for reading the different types of JOIN in SQL.

Live Demo

INTERSECT vs EXCEPT SET Operator

The INTERSECT Operator of SQL is the same as the INTERSECTION of Set Theory. So the INTERSECT is used to find the common records or rows from two tables or SELECT statements.

So the INTERSECT Operator returns only those rows which are common in both the SELECT Statement. All the rules of applying the UNION operator are also applied for INTERSECT and EXCEPT operator as well.

In the case of the INTERSECT operator as well, the order of SELECT statements does not matter, so you will get the same result in both cases.

If you see the Query Plan for the INTERSECT operator, it also uses Distinct Sorting which is one of the most costly operations in SQL. Apart from this, it is using Nested Loop (Left Semi Join) as well.

The SEMI JOINs are U-SQL’s way to filter a rowset based on the inclusion of its rows in another rowset. It is used by the SQL internally for the IN Operator. Semijoins are two types.

A LEFT SEMI JOIN gives only those rows in the left rowset that have a matching row in the right rowset. On the other hand, a RIGHT SEMI JOIN gives only those rows in the right rowset that have a matching row in the left rowset.

INTERSECT vs JOIN vs IN Clause

There are some other alternatives for the INTERSECT operation in SQL. That's why we generally do not remember to use the INTERSECT Set operator. We can use the IN Operator or INNER JOIN to achieve the same result as INTERSECT.

Please watch the INNER JOIN video here, IN vs INNER JOIN video here, and IN vs NOT IN Operator video here for more details.

As you can see, we are getting the same result for all the cases whether we are using INTERSECT operator or IN operator, or JOIN.

If you compare the Query Plan of IN and JOIN to the INTERSECT operator, both IN and JOIN do not use any sorting.

EXCEPT or MINUS Set Operator

In SQL EXCEPT operator is the same as the MINUS operation of the Set Theory. In MS SQL it is known as EXCEPT while in some other databases like ORALE, it is known as MINUS.

The Except Operator returns all the rows of the first SELECT statement excluding the common rows of both SELECT statements.

So the EXCEPT operation returns only those records which are not in the second SELECT statement or table.

As you can see the order of the table or SELECT statement matters here in the case of EXCEPT operator, so if you change the order of the SELECT statement, the result will also be changed.

If you see the Query Plan for the EXCEPT operator, it also uses the Distinct Soring and Nested Loop (Left Anti Semi Join).

The ANTI SEMI JOINs are also U-SQL’s way to filter a rowset based on the absence of its rows in another rowset. It is used by the SQL Internally for the NOT IN operator. It is also two types.

A LEFT ANTI SEMI JOIN gives only those rows in the left rowset that have no matching row in the right rowset. On the other hand, the RIGHT ANTI SEMI JOIN gives only those rows in the right rowset that have no matching row in the left rowset.

EXCEPT vs JOIN vs NOT IN Clause

There are some other alternatives for the EXCEPT operation as well in SQL. That's why we generally do not remember to use the EXCEPT Set operator. We can use the NOT IN Operator and LEFT JOIN to achieve the same result as EXCEPT.

Please watch the LEFT JOIN video here, and IN vs NOT IN Operator video here for more details.

You can see that we are getting the same result for all the cases whether we are using EXCEPT or NOT IN or LEFT JOIN.

If you see the Query Plan for NOT IN and LEFT JOIN compared to the EXCEPT operator, both are not using any sorting.

Live Demo

SQL Scripts

 


-- Create Employee Table
CREATE TABLE Employee
(
	[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](50) NOT NULL,
	[Email] [varchar](50) NULL,
	[MobileNumber] [nchar](15) NULL	
);


-- Create EmployeeArchive Table
CREATE TABLE EmployeeArchive
(
	[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](50) NOT NULL,
	[Email] [varchar](50) NULL,
	[MobileNumber] [nchar](15) NULL,
	[ArchivedOn] [datetime]	DEFAULT GETDATE(),
);

-- INSERT data into Employee Table
INSERT INTO Employee(Name, Email, MobileNumber)
VALUES ('Alex',	'alex@gmail.com', '5224578930'),
	   ('Thomas',	'thomas@gmail.com', '5229848931'),
	   ('Peter',	'peter@gmail.com', '5224578831'),
	   ('Anton',	'anton@gmail.com', '5224579937'),
	   ('Brien',	'brien@gmail.com', '5224571979');
	   
	   
-- INSERT data into EmployeeArchive Table
INSERT INTO EmployeeArchive(Name, Email, MobileNumber)
VALUES ('Alex',	'alex@gmail.com', '5224578930'),	   
	   ('Peter', 'peter@gmail.com', '5224578831'),	  
	   ('Maria', 'maria@gmail.com', '5224571976');
	   
	   
-- UNION SET OPERATOR

SELECT Name, Email, MobileNumber FROM Employee
UNION
SELECT Name, Email, MobileNumber FROM EmployeeArchive;
	   
	   
-- UNION ALL SET OPERATOR
SELECT Name, Email, MobileNumber FROM Employee
UNION ALL
SELECT Name, Email, MobileNumber FROM EmployeeArchive;


-- UNION SET OPERATOR
SELECT Name, Email, MobileNumber FROM Employee
UNION 
SELECT Name, Email, MobileNumber FROM EmployeeArchive;

-- JOIN
SELECT E.Name, E.Email, E.MobileNumber,
	   EA.Name, EA.Email, EA.MobileNumber
FROM Employee E
FULL JOIN EmployeeArchive EA ON EA.Name = E.Name;

-- INTERSECT SET OPERATOR
SELECT Name, Email, MobileNumber FROM Employee
INTERSECT
SELECT Name, Email, MobileNumber FROM EmployeeArchive;

-- After Reversing Select Statement Order
SELECT Name, Email, MobileNumber FROM EmployeeArchive
INTERSECT
SELECT Name, Email, MobileNumber FROM Employee;

-- Alternatives for INTERSECT SET OPERATOR
SELECT Name, Email, MobileNumber FROM Employee
INTERSECT
SELECT Name, Email, MobileNumber FROM EmployeeArchive;

-- Using IN() Clause
SELECT Name, Email, MobileNumber  FROM Employee
WHERE Name IN (SELECT Name FROM EmployeeArchive);
	
-- Using JOIN
SELECT E.Name, E.Email, E.MobileNumber  FROM Employee E
JOIN EmployeeArchive EA ON EA.Name = E.Name;


-- EXCEPT SET OPERATOR
SELECT Name, Email, MobileNumber FROM Employee
EXCEPT
SELECT Name, Email, MobileNumber FROM EmployeeArchive;

-- After Reversing the Select Statement Order
SELECT Name, Email, MobileNumber FROM EmployeeArchive
EXCEPT
SELECT Name, Email, MobileNumber FROM Employee;


-- Alternatives for EXCEPT SET OPERATOR
SELECT Name, Email, MobileNumber FROM Employee
EXCEPT
SELECT Name, Email, MobileNumber FROM EmployeeArchive;

-- Using NOT IN() Clause
SELECT Name, Email, MobileNumber  FROM Employee
WHERE Name NOT IN (SELECT Name FROM EmployeeArchive);	

-- Using JOIN
SELECT E.Name, E.Email, E.MobileNumber  FROM Employee E
LEFT JOIN EmployeeArchive EA ON EA.Name = E.Name
WHERE EA.Id IS NULL;

Sunday, March 6, 2022

UNION vs UNION ALL and INTERSECT vs EXCEPT SET Operators in SQL