Combining Result Sets with UNION, INTERSECT, and EXCEPT

1. The Ground Rules for Set Operators

Before we explore each operator, it's essential to understand the rules that govern all of them. These rules ensure that SQL can correctly combine or compare the result sets.

To summarize the key rules from the article:

1. Same Number of Columns: Each SELECT statement in the operation must retrieve the exact same number of columns.
2. Compatible Data Types: The data types of corresponding columns must be compatible. For instance, you can combine a VARCHAR(50) with a TEXT column, or an INT with a BIGINT. SQL will try to implicitly convert them, but it's best practice to ensure they match.
3. Column Names from the First Query: The final result set will use the column names (or aliases) from the first SELECT statement.
4. ORDER BY at the End: You can only place a single ORDER BY clause at the very end of the entire query to sort the final result set.

Keeping these rules in mind will help you avoid the most common errors when using set operators.

2. Combining Datasets: UNION and UNION ALL

The most common task is to stack one dataset on top of another. UNION and UNION ALL are the operators for this job. Though they sound similar, their difference has significant implications for both results and performance.

UNION ALL: The Simple Stack

This operator takes all the rows from the first query and appends all the rows from the second query. It's simple, fast, and includes all duplicate rows.

UNION: The Deduplicating Stack

This operator does the same as UNION ALL but with one extra step: it removes any duplicate rows from the final result set. A row is considered a duplicate if all of its column values are identical to another row.

Key Takeaway for Performance: UNION ALL is significantly faster than UNION because it doesn't need to perform a sort and comparison operation to find and remove duplicates. As an analyst, you should always prefer UNION ALL unless you have a specific reason to remove duplicates. If you're combining data from sources that you know are mutually exclusive (e.g., January sales and February sales), using UNION adds unnecessary computational overhead.

3. Comparing Datasets: INTERSECT and EXCEPT

Beyond combining data, set operators are powerful tools for comparing datasets to find overlaps and differences.

INTERSECT: Finding the Overlap

The INTERSECT operator returns only the rows that exist in both result sets. It's perfect for finding the common ground between two groups.

EXCEPT: Finding the Difference

The EXCEPT operator returns rows from the first result set that are not present in the second result set. This operator is directional: A EXCEPT B is different from B EXCEPT A. It's ideal for finding what's unique to the first group.

Note: Some SQL dialects, like Oracle, use the keyword MINUS instead of EXCEPT. The functionality is identical.

To bring all these concepts together, take a look at the following image. It provides a fantastic side-by-side comparison of all the set operators with sample data.

4. Practice Exercise

Let's apply what you've learned. Imagine you are a data analyst for an e-commerce company. You have two tables:

• active_users_january (user_id, name, last_login_date)
• active_users_february (user_id, name, last_login_date)

Your Task: Write three separate SQL queries to answer the following business questions:

1. Who are the users who were active in January but were not active in February? (These are potentially churned users).
2. Who are the loyal users that were active in both January and February?
3. What is the complete list of all user activity across both months? Include duplicate entries if a user was active in both.

Think carefully about which set operator is the right tool for each question.

SELECT user_id, name FROM active_users_january
EXCEPT
SELECT user_id, name FROM active_users_february;

SELECT user_id, name FROM active_users_january
INTERSECT
SELECT user_id, name FROM active_users_february;

SELECT user_id, name, last_login_date FROM active_users_january
UNION ALL
SELECT user_id, name, last_login_date FROM active_users_february
ORDER BY user_id, last_login_date;

Conclusion

Congratulations on completing the "Intermediate SQL for Analytics" module! In this lesson, you've learned how to use SQL's set operators to manage entire result sets, a skill that's distinct from but just as important as the row-level logic of JOINs and window functions.

Key Takeaways:

• Set operators like UNION, INTERSECT, and EXCEPT combine or compare rows from multiple SELECT statements.
• They require the SELECT statements to have the same number of columns with compatible data types.
• UNION combines results and removes duplicates; UNION ALL combines results but keeps duplicates, making it much faster.
• INTERSECT returns only the rows that are common to all result sets.
• EXCEPT returns rows from the first result set that are not in the second, making it useful for finding differences or "churn".

You now have a robust set of SQL skills for complex data retrieval and manipulation.

In our next module, "Advanced SQL and Performance Tuning," we will take your skills to the next level. We'll begin by exploring correlated subqueries, which allow for sophisticated row-by-row comparisons across tables, and then dive into how to analyze and optimize your queries for better performance.