Mastering SQL Set Operators: UNION, INTERSECT, UNION ALL, And EXCEPT Explained With Examples

SQL Set operators are used to combine the results of two or more SELECT statements into a single result set. There are three SQL Set operators:

UNION: The UNION operator is used to combine the results of two or more SELECT statements into a single result set. It removes duplicates and returns only distinct values.

Example:

sql

Copy code
SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2;        

INTERSECT: The INTERSECT operator is used to combine the results of two or more SELECT statements and returns only the common rows between them. It also removes duplicates and returns only distinct values.

Example:

sql

Copy code
SELECT column1, column2 FROM table1 INTERSECT SELECT column1, column2 FROM table2;        

EXCEPT or MINUS: The EXCEPT operator is used to combine the results of two SELECT statements and returns only the rows that are present in the first SELECT statement and not in the second SELECT statement. It also removes duplicates and returns only distinct values.

Example:

sql

Copy code
SELECT column1, column2 FROM table1 EXCEPT SELECT column1, column2 FROM table2;        

Note: The MINUS keyword can be used instead of EXCEPT in some databases like Oracle.

UNION ALL is a variation of the UNION operator in SQL. While UNION removes duplicates and returns only distinct values, UNION ALL allows duplicates to remain and includes all rows from both SELECT statements.

Example:

sql

Copy code
SELECT column1, column2 FROM table1 UNION ALL SELECT column1, column2 FROM table2;        

In this example, the result set will include all rows from both table 1 and table 2, including any duplicates that may exist between them.

UNION ALL can be useful when you need to combine two result sets without removing duplicates or when you know that there are no duplicates in the result sets and you want to avoid the overhead of removing them. However, UNION ALL may return a larger result set than UNION and may take longer to execute, especially when working with large datasets.

1. What is the difference between UNION and UNION ALL?

Ans. UNION and UNION ALL are both SQL Set operators used to combine the results of two or more SELECT statements into a single result set, but they have some important differences:

• UNION removes duplicates from the result set, while UNION ALL includes all rows from both SELECT statements, even if there are duplicates.
• UNION creates a new result set with unique values based on all selected columns, while UNION ALL simply appends the rows from the second SELECT statement to the bottom of the first SELECT statement.
• UNION is a more resource-intensive operation than UNION ALL because it requires the database to sort and compare all the rows in both SELECT statements, while UNION ALL simply concatenates the rows without any additional processing.

In summary, UNION ALL is faster and less resource-intensive than UNION, but it may return duplicate rows, whereas UNION removes duplicates but may be slower and more resource-intensive. Which operator to use depends on the specific requirements of the query and the data being queried.

2. What is the difference between UNION and INTERSECT?

• UNION and INTERSECT are both SQL Set operators used to combine the results of two or more SELECT statements into a single result set, but they have some important differences:
• UNION combines rows from two or more SELECT statements into a single result set, while INTERSECT returns only the common rows between two SELECT statements.
• UNION removes duplicates from the result set, while INTERSECT also removes duplicates, returning only distinct values.
• UNION and INTERSECT require that the SELECT statements have the same number of columns and compatible data types.

In summary, UNION returns all rows from both SELECT statements and removes duplicates, while INTERSECT returns only the rows that are present in both SELECT statements and removes duplicates. Which operator to use depends on the specific requirements of the query and the data being queried.

3. Can you give an example of when you would use the INTERSECT operator?

Ans. 

here's an example of when you would use the INTERSECT operator:

Suppose you have two tables, sales2019, and sales2020, with the same columns: product_name, quantity, and revenue. You want to find the products that were sold in both 2019 and 2020 and the total quantity and revenue for each product across both years. Here's how you could use the INTERSECT operator to accomplish this:

sql

Copy code
SELECT product_name, SUM(quantity) AS total_quantity, SUM(revenue) AS total_revenue 
FROM sales2019 
WHERE product_name IN 
( SELECT product_name FROM sales2020 INTERSECT SELECT product_name FROM sales2019 ) GROUP BY product_name;        

In this query, the INTERSECT operator is used to find the products that were sold in both sales2019 and sales2020. The result of the INTERSECT is a list of distinct product names that appear in both tables. This list is then used as a filter in the outer query to sum the quantity and revenue for each product across both years.

Note that in order for the INTERSECT operator to work, the SELECT statements in the query must have the same number of columns and compatible data types.

Can you explain how the ORDER BY clause works with SQL Set operators?

The ORDER BY clause can be used with SQL Set operators like UNION, UNION ALL, INTERSECT, and EXCEPT to sort the resulting data set by one or more columns. The ORDER BY clause is applied to the entire result set after the Set operator is used to combine the results of two or more SELECT statements. The resulting data set will inherit the column names and data types from the first SELECT statement in the query. The ORDER BY clause can reference any column in the resulting data set, including columns that were not selected in the individual SELECT statements.