Set Operators

Combine results

Introduction

SQL logo

Set operators in SQL are used to combine results from two or more queries into a single result set. These operators enable you to manipulate query results similarly to how you work with mathematical sets. The main set operators supported by MS SQL are:

  • UNION and UNION ALL
  • INTERSECT
  • EXCEPT

Each set operator has specific rules for how it handles rows and duplicates, and each works only when the queries being combined have the same number of columns and compatible data types.

Common Set Operators

  1. The UNION Operator

The UNION operator combines the results of two or more SELECT queries and returns distinct rows. This means any duplicate rows appearing in both result sets are automatically removed.

Syntax:

SELECT column_list FROM table1
UNION
SELECT column_list FROM table2;

Example: Retrieve a list of cities where customers and suppliers are located.

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers;

In this example, UNION eliminates duplicate city names, showing each city only once.

  1. The UNION ALL Operator

UNION ALL works similarly to UNION but does not remove duplicates. All rows from both result sets, including duplicates, are returned.

Syntax:

SELECT column_list FROM table1
UNION ALL
SELECT column_list FROM table2;

Example: Retrieve all cities from both customer and supplier tables, including any duplicates.

SELECT city FROM Customers
UNION ALL
SELECT city FROM Suppliers;

Here, unlike UNION, UNION ALL allows the display of cities that appear in both tables without removing duplicates.

  1. The INTERSECT Operator

The INTERSECT operator returns only the rows that appear in both result sets. It’s used when you want to find common elements between two queries.

Syntax:

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;

Example: List the cities where both customers and suppliers are located.

SELECT city FROM Customers
INTERSECT
SELECT city FROM Suppliers;

In this case, only the cities that appear in both the Customers and Suppliers tables will be included in the result.

  1. The EXCEPT Operator

The EXCEPT operator returns rows from the first query that are not present in the second query. It’s helpful for identifying data that exists in one set but not the other.

Syntax:

SELECT column_list FROM table1
EXCEPT
SELECT column_list FROM table2;

Example: Find cities where customers are located but not suppliers.

SELECT city FROM Customers
EXCEPT
SELECT city FROM Suppliers;

Here, the result will contain cities that appear in the Customers table but are absent in the Suppliers table.

Rules for Set Operators

  • Same Number of Columns: The queries combined using set operators must have the same number of columns.
  • Compatible Data Types: The columns being compared between the queries must have compatible data types.
  • Order By Clause: The ORDER BY clause can only appear once, at the end of the entire set operation, not after individual queries.

Example: Combine two result sets and order them by city name.

SELECT city FROM Customers
UNION
SELECT city FROM Suppliers
ORDER BY city;

Use Cases for Set operators

  • UNION: Use UNION when you need to consolidate different customer data sources, such as active and inactive customers, and remove duplicates.

    Example: Combine lists of current and former customers.

    SELECT first_name FROM ActiveCustomers
UNION
SELECT first_name FROM FormerCustomers;

  • INTERSECT: Use INTERSECT when you want to find overlapping data, such as customers who have made purchases from multiple departments.

    Example: Customers who bought from both electronics and apparel.

    SELECT customer_id FROM ElectronicsPurchases
INTERSECT
SELECT customer_id FROM ApparelPurchases;

  • EXCEPT: Use EXCEPT to find differences, such as customers who have not yet made any purchases.

    Example: List customers who registered but haven’t made a purchase.

    SELECT customer_id FROM RegisteredCustomers
EXCEPT
SELECT customer_id FROM Purchases;

NULL values with Set operators

  • UNION: Considers NULL values and treats them like any other value when removing duplicates.
  • UNION ALL: Includes NULL values in the final result, but will not remove duplicates.
  • INTERSECT: Includes rows where NULL values match in both queries.
  • EXCEPT: Returns rows where NULL values exist in the first query but not in the second, or where the matching row has a non-NULL value.

Best Practices

  1. Ensure Matching Columns: Both queries in a set operation must return the same number of columns with the same data types in the same order.

  2. Use UNION ALL When Duplicates Are Needed:
    UNION eliminates duplicates, which can be costly in terms of performance. Use UNION ALL if you don't need to remove duplicates for better performance.

  3. Use INTERSECT and EXCEPT Judiciously:
    INTERSECT and EXCEPT can be resource-intensive as they involve comparisons between sets. Use these operators only when necessary to optimize query performance.

  4. Ensure Consistent Data Types:
    The columns being compared in the set operations should have the same or compatible data types. Otherwise, SQL will throw an error or implicitly convert types, potentially leading to performance issues.

  5. Be Mindful of NULL Values:

    • INTERSECT and EXCEPT treat NULL values differently compared to UNION. Ensure your queries handle NULL values as expected, especially in comparisons.

Conclusion

Set operators in MS SQL are powerful tools for combining and comparing result sets. By using UNION, INTERSECT, and EXCEPT, you can handle different types of data operations, such as merging data, finding commonalities, or identifying gaps. These tools are especially valuable for customer management tasks, helping businesses manage large datasets efficiently and gain meaningful insights.

Asked in Interview

  • What are the different Set Operators available in SQL?
  • What is the difference between UNION and UNION ALL?
  • Can you explain the INTERSECT operator with an example?
  • What does the EXCEPT operator do in SQL?
  • What are the requirements for using Set Operators in SQL?
  • How do NULL values behave with Set Operators?
  • How can using Set Operators impact performance?
  • You have two result sets. One contains a list of products from Warehouse1, and the other contains products from Warehouse2. Write a query to return all products from both warehouses, including duplicates