Overview
SQL Set Operations &
Advanced Filtering Practice
Combining query outputs, validating relationships, applying strict and flexible conditions, and moving data between tables.
Course Learning Objectives
- Use UNION to combine multiple SELECT query results into a single deduplicated output, following the column count and type compatibility rules.
- Use UNION ALL to combine complete query outputs while preserving all records including duplicates, for operational data stacking and frequency analysis.
- Use EXISTS to filter records based on whether related data exists in a connected table, using correlated subqueries.
- Use ANY to compare a value against multiple subquery results where the condition must hold for at least one — the flexible threshold operator.
- Use ALL to compare a value against every subquery result where the condition must hold for all — the strict threshold operator.
- Use INSERT INTO SELECT to copy records from one table into another using a SELECT query, with filtered copying via WHERE.
Part 1 covers UNION — combining results from multiple SELECT queries into a single deduplicated output. Students learn the three UNION rules (same column count, compatible types, consistent order), understand that the output column name comes from the first SELECT, and apply UNION to merge business datasets.
Merging records from different tables or queries into a single output list.
UNION automatically deduplicates — each unique value appears once regardless of how many queries produce it.
Building unified reference lists by combining IDs, names, and values from multiple business tables.
Merging financial values, names, and categories from different tables into a consolidated report.
- Practice focus: combining names, IDs, categories, and financial values across users, products, orders, transactions, subscriptions, support tickets, and campaigns
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 2 covers UNION ALL — the faster variant that keeps every record including repeated values. Students learn when to choose UNION ALL over UNION, how it enables operational data stacking, and why it is preferred for frequency analysis and complete audit trails.
UNION ALL keeps every row from every SELECT — no deduplication is performed.
When repeated values are valid data that must not be removed from the combined output.
Merging complete datasets from two tables into one for full operational coverage.
Why UNION ALL is faster than UNION and when that performance advantage matters most.
- Practice focus: stacking names, IDs, categories, statuses, and financial values across all major business tables with all records preserved
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 3 covers EXISTS — the relational filter that checks whether a subquery returns any rows. Students learn the correlated subquery pattern, understand that EXISTS returns only outer table columns, and apply EXISTS to validate relationships across all major table pairs.
EXISTS evaluates TRUE when the subquery finds at least one match; FALSE when it finds none.
Confirming business relationships — users with transactions, products with orders, campaigns with events.
The correlated WHERE clause that links the subquery to each row of the outer table.
EXISTS across all table relationships — users, products, campaigns, orders, and sessions.
- Practice focus: validating relationships across users-transactions, users-orders, products-order_items, users-subscriptions, users-tickets, campaigns-events, users-sessions, and orders-order_items
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 4 covers ANY — the flexible subquery comparison operator. Students learn that > ANY beats the minimum, < ANY is below the maximum, and = ANY is equivalent to IN. They apply ANY for cross-table value comparisons across products, transactions, subscriptions, and order items.
ANY returns TRUE if the condition holds for at least one subquery value — the minimum threshold.
> ANY = above the minimum. < ANY = below the maximum. = ANY = equivalent to IN.
Cross-table comparisons — transaction amounts vs subscription fees, product prices vs transaction amounts.
Identifying records that exceed the minimum benchmark in a comparison group.
- Practice focus: ANY comparisons across products, transactions, subscriptions, and order_items using > ANY and < ANY operators
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 5 covers ALL — the strictest subquery comparison operator. Students learn that > ALL equals > MAX, < ALL equals < MIN, and that zero rows is a valid result when no value satisfies the strict condition. They apply ALL for extreme performance comparisons across business datasets.
ALL returns TRUE only if the condition holds for every single subquery value — the strictest test.
> ALL = above the maximum. < ALL = below the minimum. != ALL = not in the list.
Identifying top records that exceed the entire comparison group — the highest performers.
Zero rows from ALL is analytically meaningful — no value satisfies the strict cross-group condition.
- Practice focus: ALL comparisons across products, transactions, subscriptions, and order_items; understanding when zero rows is the correct and expected result
- 8 practice queries — each with an AI prompt and a validated SQL answer including notes on expected empty results
Part 6 covers INSERT INTO SELECT — the SQL command for copying records between tables. Students learn the two-step pattern (CREATE TABLE then INSERT), how WHERE filters which rows are copied, and that source data is never changed. Each exercise uses DROP TABLE IF EXISTS for clean repeat runs.
INSERT INTO SELECT copies rows from a source table into a pre-existing destination table.
Moving business data between tables as part of archive, reporting, and ETL workflows.
The two-step pattern: CREATE TABLE first, then INSERT INTO destination SELECT from source.
Filtered copying with WHERE — completed orders, active subscriptions, open tickets, Email campaigns.
- Practice focus: copying users, completed orders, active subscriptions, refund transactions, open tickets, Email campaigns, Desktop sessions, and Software products into archive tables
- 8 two-step practice exercises — CREATE TABLE then INSERT INTO SELECT, each with an AI prompt and validated SQL
Course at a Glance
Six parts · 48 practice queries · same tables as Parts 1–5
| Part | Command / Operator | Core Focus | Practice |
|---|---|---|---|
| Part 1 | UNION | Combine queries and deduplicate — names, IDs, categories, financial values | 8 Queries |
| Part 2 | UNION ALL | Stack all records including duplicates — faster than UNION, preserves frequency | 8 Queries |
| Part 3 | EXISTS | Relational existence checks — filter by whether related activity exists in a linked table | 8 Queries |
| Part 4 | ANY | Flexible threshold — TRUE if condition holds for at least one subquery value | 8 Queries |
| Part 5 | ALL | Strict threshold — TRUE only if condition holds for every subquery value; zero rows is valid | 8 Queries |
| Part 6 | INSERT INTO SELECT | Copy and archive records — CREATE TABLE then INSERT with WHERE filters | 8 Queries |
Every practice question in this course includes a business question, an AI prompt for ChatGPT or Copilot, the validated SQL answer, and guidance on interpreting the result. Students learn to prompt effectively, validate advanced SQL logic, troubleshoot subquery conditions, and connect set operations and data movement to real analytical workflows.
What You Leave With
Six advanced SQL operations. Forty-eight practice queries. An enterprise SQL toolkit.
Clean deduplicated merging of multiple query results for unified business reporting.
Fast, complete data stacking that preserves every record including repeated values.
Relational existence filtering — validating whether linked business activity is present.
Flexible threshold comparison — TRUE if the condition holds for at least one value.
Strict threshold comparison — TRUE only if the condition holds for every value.
Automated record copying, archiving, and data movement between business tables.
Prompting, validating, and troubleshooting advanced SQL in a professional analytical context.
Certificate of Completion from the Canadian Institute of Artificial Intelligence (CiAI), powered by DataSoSi.
