SQL Set Operators Made Simple: UNION, UNION ALL, INTERSECT, MINUS and EXCEPT FAQs

1.What are set operators in SQL and how they work?
SQL uses set operators in order to combine results coming from two or more SELECT queries. They are set theory based and are explicitly designed to work on whole result sets, not columns. Here are the most important SQL set operators: UNION, INTERSECT, UNION ALL, EXCEPT and MINUS. These operators enable you to combine, subtract or find common data between multiple result sets. For set operators to be used, the queries involved should return the same number of columns and the columns should have compatible data types.
For instance, the UNION operator combines results from two SELECT queries, removing duplicates and UNION ALL retains duplicates. INTERSECT operator returns common rows in both result sets and EXCEPT returns rows that exist in the first result set and do not exist in the second.
Example:
SELECT name FROM employees
UNION
SELECT name FROM contractors;
This combines names from the employees and the contractors table without duplicates.

2.What's the difference between UNION and UNION ALL?
The critical difference between UNION and UNION ALL is based on its treatment of duplicate records. UNION removes duplicate records as it combines two or more SELECT queries into results. it, therefore eliminates duplicated records in the final data set.
Instead, UNION ALL merges two or more SELECT queries into one, without eliminating any duplicate values. It carries all rows but can include a row more than once.
Performance is greatly varied.
UNION requires overhead in order to eliminate the duplicate. It could therefore slow up the execution of a query in a database system for a very large set of data. For its part, UNION ALL is faster as it merely combines the result of the two queries, with no overhead to remove duplicates.
Illustration
SELECT product_name FROM products1
UNION ALL
SELECT product_name FROM products2;
This returns a list of product names from both tables, repeating duplicates.

3.How does the INTERSECT operator work in SQL?
The INTERSECT operator compares two result sets by performing set-intersection. It produces an output of only rows unique to the two selected expressions. It compares results obtained from two SELECT statements to return the rows present in both. INTERSECT is very useful where the retrieval of records that also happen in both tables or data sheets is needed. Unlike UNION, the INTERSECT operator must also ensure both queries are returning the same number of columns all compatible in data type. The operation removes duplicates automatically so you do not get duplicate rows for each matching row in the result.
INTERSECT is a valuable operator when you want to obtain a dataset showing overlaps from more than one sources or datasets. It works kind of like an intersection on a Venn diagram - only the common elements will be returned.
Example
SELECT customer_id FROM orders1
INTERSECT
SELECT customer_id FROM orders2;
This query will return customer IDs that are in both orders1 and orders2.

4.What is the EXCEPT operator in SQL?
The EXCEPT operator is used to return rows from the first SELECT statement that are not found in the second SELECT statement. In other words, it subtracts one result set from another. It works like INTERSECT, but instead of returning common records, it returns the difference between the two datasets. Like INTERSECT the queries involved must return the same number of columns with compatible data types and duplicates are automatically removed.
The EXCEPT operator is one of the most useful operators, it helps in finding the records that exist in one table but not in the otherwhich makes it quite useful for comparison or exclusion operations in data.
SELECT employee_id FROM employees
EXCEPT
SELECT employee_id FROM contractors;
This returns all the employee IDs available in the employees table and are not present in the contractors table.

5.What does UNION do to duplicate values?
UNION removes duplicate rows automatically as it eliminates duplicates from the result set obtained by combining two or more SELECT statements. The procedure to remove duplicate rows is applying the DISTINCT operation on the results combined together. It removes one if it is identical in both the SELECT statements. This operation ensures that the result set contains only unique rows, hence it is used very much for generating a consolidated list of distinct records from multiple queries.
However, removing duplicates can be very expensive in terms of performance. The database needs to sort and compare all the rows from both queries; this makes the process more time-consuming than when one uses UNION ALL as it does not remove the duplicates.
Example:
SELECT name FROM employees
UNION
SELECT name FROM contractors;
This query returns unique names from the employees and contractors tables.

Comprehensive guide to SQL set operators: UNION, UNION ALL, INTERSECT, MINUS and EXCEPT with easy-to-follow examples

6.How do you use the INTERSECT operator to identify common records?
The INTERSECT operator can be used to determine which records are common to both datasets. It takes two result sets from two different queries and returns only those rows that exist in both data sets. The INTERSECT operator is handy for determining overlapping data among several tables, such as a customer who has made an order in two separate time periods or items found under multiple categories.
This operator results in distinct common rows that would come out therefore appropriate if you want to display overlapping records between two sets of results.
Example;
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM returns ;
customer ids who have had a placement of orders and returns are given because these are the overlapping customer lists of the orders vs the returns datasets.

7.What is the difference between set operators and joins in SQL?
UNION, INTERSECT, EXCEPT and MINUS set operators in SQL differ from joins on how data is combined. Set operators combine all rows from one or more result sets using set theory, while joins combine data based on common columns between tables. Joins are based on row-by-row matching based on some specified conditions, while set operators combine all rows from a result set using union, intersection or difference. Joining is also more flexible since it will allow you to define a complex relationship between the tables, while set operators are more for combining or subtracting result sets.
Another difference is that set operators automatically eliminate duplicates (except for UNION ALL), while joins can return duplicate rows if the join conditions match multiple rows.

8.Can we use different numbers of columns in set operators?
No, set operators require that all the SELECT statements involved return the same number of columns with compatible data types. That means that the operator will perform the appropriate set operation-union, intersection or difference-correctly. If the number of columns in the SELECT statements do not match SQL will raise an error. This is one of the major restrictions working with set operators places on your type of queries you can join together.
For instance, you cannot union a SELECT query with two columns with a query that returns only one column because the set operator won't know how to align the results.
Example:
SELECT name, age FROM employees
UNION
SELECT name FROM contractors; -- This will raise an error due to mismatched columns.

9.What is the MINUS operator in SQL? How does it work?
Oracle SQL's MINUS operator returns rows from the first SELECT statement that are not present in the second SELECT statement. The Oracle SQL MINUS is much like the SQL Server and PostgreSQL EXCEPT operators. MINUS lets one result set be subtracted from another. If it is not explicitly mentioned in a query, the query returns only distinct rows by automatically eliminating duplicates. MINUS basically helps to find the difference between two datasets.
Example:
SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM contractors;
This returns the employee IDs from the employees table that are not found in the contractors table.

10.How would you deal with NULLs with set operators?
The NULL values are considered equal in comparison of rows of two result sets by the set operators. For example, if the result sets present NULL in both of them then it would be considered as a match for INTERSECT, EXCEPT and UNION. In other words NULL is treated just like every other value in SQL set operations and it is compared as equal to another NULL in another result set. This can sometimes result in unexpected outcomes, particularly when you are not explicitly accounting for NULL values.
If NULLs require special treatment you may wish to make use of a replacement functions like COALESCE or IFNULL for assigning some specific value for NULL values so that set operators will be able to perform desired operations on such NULLs.

11.Are there any subqueries to set operators?
Yes, subqueries can be used along with set operators. Subquery can be any result of any SELECT statement and it can be used in conjunction with the set operators, UNION, INTERSECT, EXCEPT or MINUS. It's very helpful in situations when one wants to compare or combine some results of complex queries, avoiding temporary tables or views. The subqueries also have to adhere to set operators rules meaning they will return the same number of columns and compatible data type.
for example.
SELECT employee_id FROM department1
UNION
SELECT employee_id FROM (SELECT employee_id FROM department2 WHERE status = 'active');
This combines employees from department1 with those from a subquery that retrieves active employees from department2.

12.What is the performance difference between UNION and UNION ALL?
The performance difference while using UNION versus UNION ALL can be pretty dramatic, of course, depending on really large data sets. UNION basically performs a DISTINCT operation on a result set to remove duplicates which takes extra processing, involving sorting data and comparing rows to find the duplicate. And that makes UNION slower as UNION ALL does not perform this step. Remove duplicates and only merges the result sets as it is.
If you're sure there are no duplicates in the result sets or you don't mind having them, UNION ALL will be much faster because it doesn't have to incur the extra overhead of removing duplicates.

13.How do you combine ORDER BY with set operators?
You can still use ORDER BY in any queries with set operators. Though, the ORDER BY should be positioned at the final SELECT query. It comes into play on the result set as a whole after it has performed all set operations not on individual SELECT queries. This means that there will be an ORDER BY of the collective results after performing all the set operations.
Use an example
SELECT product_name FROM products1
UNION
SELECT product_name FROM products2
ORDER BY product_name;
This query joins records from products1 and products2 and sorts the product_name.

14.What are the disadvantages of using set operators in SQL?
There are limitations for set operators. They need queries involved to return the same number of columns and those columns have compatible data types. Furthermore by default, the set operators eliminate duplicates except in UNION ALL which creates overhead if large datasets exist. The last limitation for set operators is that it is impossible to compare or join tables on conditions as JOIN operations may allow it. Finally, since set operations compare entire rows, they will not be suitable for even more complex queries that require detailed row matching based on specific columns.

15.Can aggregate functions be used using set operators?
Yes. You can use aggregate functions such as SUM, COUNT, AVG, MIN, and MAX with set operators too, but you must know how they actually work together. Set operators interact with aggregate functions when used with aggregate functions; aggregation occurs for every query first, and the set operator then acts after aggregating. You will apply GROUP BY along with aggregate functions so that the result sets are of the same structure before you can apply set operators to them.
For example,
SELECT department, SUM(salary) FROM employees1
GROUP BY department
UNION
SELECT department, SUM(salary) FROM employees2
GROUP BY department;
This query adds up the salary for each department from two tables and gives the results.results.

16.What happens when columns do not match when using set operators?
When using set operators in SQL, the number of columns and their data types must match between all SELECT queries. If columns do not match, SQL will throw an error. This can happen if one SELECT query returns more columns than another or if columns have incompatible data types. In this cases, you need to be sure that each of your queries returns an equal amount of columns that are convertible. To this, some techniques may help, which include:
NULL values use for missing columns in a query.
Castings of your columns explicitly to the possible data types; e.g. using CAST() or CONVERT(). As a variant of the preceding example use the following
SELECT employee_id, name FROM employees
UNION
SELECT employee_id, CAST(age AS VARCHAR(50)) FROM contractors;
This ensures that the columns returned by both queries are compatible by casting age as VARCHAR in the second query.

17.How do set operators work between different data types?
Set operations require the data types on corresponding columns to be exactly the same in all select statements. If they aren't the same an SQL error is generated. Over this, you can cast them or rewrite the select statement to return the same types. This has the implication that SQL might implicitly convert them to some common type when the types are implicitly compatible, like INTEGER and FLOAT. Where they are not implicitly compatible, explicit casting will need to be done.
Example:
when VARCHAR and INTEGER are used in the same column position, it is erroneous unless one is cast to match the other.
SELECT product_id, product_name FROM products
UNION
SELECT product_id, CAST(description AS VARCHAR(100)) FROM products2;
here, description is cast to VARCHAR to match column type of the first query.

18.How does DISTINCT affect the outcome of set operations?
DISTINCT is applied automatically in a UNION and INTERSECT operations so that duplicate rows are eliminated by default. However, you must be aware that DISTINCT introduces extra processing time for your query because SQL has to check each row for its uniqueness. If you still want to keep all those duplicate records in the overall result, you should opt for UNION ALL instead of UNION. When using the set operators, DISTINCT is already a part of the operation, so adding it explicitly does not change the result but may cause performance problems.
Example:
SELECT customer_id FROM orders
UNION
SELECT customer_id FROM returns;
This query automatically removes duplicates between the orders and returns tables.

19.What are the uses of set operators with aggregate functions?

You can use set operators along with aggregation functions like SUM(), COUNT(), AVG(), etc., but you should apply them in proper usage. When you employ set operators the aggregated function will first calculate within each query and after that the set operator will be applied on it. If you're using an aggregate then GROUP BY should accompany each query to ensure the groups match between the result sets.
SELECT department, SUM(salary) FROM employees1
GROUP BY department
UNION
SELECT department, SUM(salary) FROM employees2
GROUP BY department;
This SQL query computes sum of salaries by department for both the tables and brings together all those results, filtering out just one unique set of department-salary pairs.

20.Is UNION applicable to other types of non-select statements such as INSERT or UPDATE?
Though the operators of the UNION set are primarily applied for combining result sets returned from the SELECT statements, it cannot be directly applied on the INSERT or UPDATE statement. However, you can apply the UNION in the SELECT query to get the data to use it later in the INSERT or UPDATE statement. For example, you may have a UNION of two SELECT statements, where the two queries will prepare a combined result set. The prepared combined result set will be used and inserted into a table.
Example:
INSERT INTO all_employees (employee_id, name)
SELECT employee_id, name FROM employees1
UNION
SELECT employee_id, name FROM employees2;
This SQL statement combines two SELECT queries and inserts the result into the all_employees table.

Previous Topic==> Managing Views FAQ. || Next Topic==> Indexes SQL FAQ.

Top SQL Interview Questions Employee Salary Management FAQ!. Top 25 PL/SQL Interview Questions
Topics for Account Management Case Study
CASE Study SQL (Account Management)
Joins With Group by Having Equi Join Joins with Subqueries Self Join Outer Join