Register

SQL: Set Operations

Set operations in SQL—including UNION, UNION ALL, INTERSECT, and EXCEPT—are essential tools analytics engineers utilize to combine, compare, and differentiate query results. These operations help create comprehensive datasets, identify commonalities, or highlight differences across various result sets.

In this tutorial, “SQL Exercises: Set Operations,” you’ll gain proficiency in leveraging these powerful operations to manipulate and analyze data effectively. By mastering set operations, you can seamlessly merge data from multiple sources, quickly find overlapping data, or isolate unique information, greatly enhancing the depth and flexibility of your analytics.

🚀 Jump Right to Exercise Tasks: SQL Exercises – Set Operations

Combining Distinct Results with UNION

Imagine you’re compiling customer lists from different marketing campaigns. Using UNION, you can merge these lists into one comprehensive set without any duplicates.

Practical Example

SELECT email FROM campaign_a_customers
UNION
SELECT email FROM campaign_b_customers;

Example Solution Explained:

This query merges emails from two separate campaigns, automatically removing duplicates, giving you a unified customer outreach list.

Example Output:

email
-------------------
john@example.com
alice@example.com
mike@example.com

Key Takeaways:

  • UNION combines results without duplicates.
  • Perfect for clean, unified datasets.

Combining Results Including Duplicates (UNION ALL)

Suppose you are analyzing transactions from two periods. Using UNION ALL, you retain duplicates, allowing detailed tracking and accurate counts.

Practical Example

SELECT order_id FROM january_orders
UNION ALL
SELECT order_id FROM february_orders;

Example Solution Explained:

This query combines orders from January and February, maintaining duplicates, useful for full transaction visibility.

Example Output:

order_id
---------
101
102
101  -- duplicate retained

Key Takeaways:

  • UNION ALL includes all duplicates.
  • Ideal for complete transactional records.

Finding Common Results with INTERSECT

Imagine you’re comparing product sales between two distinct time periods. Using INTERSECT, you can quickly identify products sold in both periods, highlighting consistently popular items.

Practical Example

SELECT product_id FROM sales_q1
INTERSECT
SELECT product_id FROM sales_q2;

Example Solution Explained:

This query returns only products appearing in both quarterly sales, providing immediate insight into sustained demand.

Example Output:

product_id
----------
2001
2005

Key Takeaways:

  • INTERSECT identifies common entries.
  • Useful for pinpointing consistent patterns.

Highlighting Differences with EXCEPT

Consider a scenario where you want to identify customers who bought products last year but haven’t made a purchase this year. Using EXCEPT, you can efficiently isolate these specific customers for targeted re-engagement campaigns.

Practical Example

SELECT customer_id FROM purchases_2022
EXCEPT
SELECT customer_id FROM purchases_2023;

Example Solution Explained:

This query returns customers who were active last year but absent this year, highlighting opportunities for re-engagement.

Example Output:

customer_id
------------
5001
5008

Key Takeaways:

  • EXCEPT helps identify unique differences.
  • Critical for targeted analytics and outreach.

What You’ll Gain from Completing This Exercise

Mastering SQL set operations equips you with the skills to effectively manage and analyze combined data sets, helping uncover meaningful patterns, commonalities, and unique insights.

Earn XP, Unlock Rewards, and Track Progress!

Log in to earn XP, unlock exciting rewards, and automatically track your progress as you advance your skills in SQL set operations!

SQL Exercises: Set Operations

SQL Exercises: Set Operations

Ask Tutor