SQL Set Operators Complete details( Union, Union ALL, Intersect, Minus)

 SQL Set Operators: A Comprehensive Guide for Interviews

In this post, I will provide a detailed explanation of SQL SET Operators and a list of common interview questions on the topic. Before reading further, I recommend that you familiarize yourself with the basic concept of SQL SET Operators and practice using them on your own test data.

This will help you to understand the different types of SET Operators and how they work, and to be prepared to answer any type of question that may be asked in an interview.

 

SQL set operators used for comparison of rows from multiple tables or a combination of results from multiple queries

SQL provides a range of set operators, including UNION, UNION ALL, INTERSECT, and EXCEPT, which can be used to perform specific functions.

 UNION

•       Combines the results of two or more SELECT Statements.

•       Removing duplicate rows.

•       sort all the elements in the results table

•       Union is slower than Union All

 

UNION ALL

•       Combines the results of two or more SELECT statements.

•       It including duplicate rows.

•       doesn't sort the data

•       It is faster than Union

INTERSECT

•       Returns only the rows that are present in all of the SELECT statements.

 

 

MINUS

•       Returns only the rows that are present in the first SELECT statement but not in the second SELECT statement.

 

RULES for SET Operators

 

•       The number of columns in the SELECT statement on which you want to apply the SQL set operators must be the same.

•       The order of columns must be in the same order.

•       The selected columns must have the same data type.

•       If you want to order/sort the results, the ORDER BY clause must go at the end of the last query. You can't add ORDER BY inside each SELECT query before the set operators.

 

U1	U12		U2	U21
1	2		1	3
2	3		null	null
3	5		null	3
4	6		2	1
			3