EXCEPT, INTERSECT, UNION — For Diffs and Dedup

When self-joins are overkill — the set operations every analyst should reach for. FIND_VIDEO: search 'SQL EXCEPT INTERSECT UNION ALL' — recommended channel: Modern SQL. Aim for 9 min or under.

11 minutesVideo LessonPDF notes

Watch EXCEPT, INTERSECT, UNION — For Diffs and Dedup on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Set Operation Overview — Introduction to sample tables and structural requirements for SQL set operations.
UNION Demonstration — Executing UNION to combine two datasets while removing overlapping duplicate records.
UNION ALL Execution — Running UNION ALL to demonstrate full dataset concatenation with duplicate rows preserved.
INTERSECT Operation — Using INTERSECT to isolate rows that exist concurrently in both tables.
EXCEPT (A - B) — Demonstrating set subtraction from u1 to u2 to extract unique left-table rows.
EXCEPT (B - A) — Inverting the EXCEPT query order to extract rows unique to the right table.

PDF notes

Bottom line: Master SQL set operations—UNION, UNION ALL, INTERSECT, and EXCEPT—to combine, deduplicate, and find row-level differences across queries. You will learn the structural prerequisites for set queries and how ordering alters asymmetric difference operations.

This lesson on EXCEPT, INTERSECT, UNION — For Diffs and Dedup is hands-on and example-driven. Master SQL set operations—UNION, UNION ALL, INTERSECT, and EXCEPT—to combine, deduplicate, and find row-level differences across queries. You will learn the structural prerequisites for set queries and how ordering alters asymmetric difference operations.

What You'll Be Able To Do

Combine disparate row sets while removing duplicates using UNION
Preserve duplicate records across datasets using UNION ALL
Identify common rows across multiple datasets using INTERSECT
Isolate asymmetric differences between datasets using directional EXCEPT queries
Verify column count and data type compatibility before executing set operations

Detailed Concept Walkthrough

1. Set Operation Structural Prerequisites

Set operators process entire result sets vertically rather than joining them horizontally. Both input queries must share compatible structural schemas to allow row alignment.

Syntax Rule: Every query participating in a set operation must select the exact same number of columns in identical positional order. SQL aligns data by column position rather than column names.
Type Compatibility: Corresponding positional columns between queries must have identical or implicitly convertible data types. If types mismatch, the database engine raises a type mismatch error during execution.
Best Practice: Explicitly alias columns in the first SELECT statement, as the top query's column names dictate the output schema headers for the combined result set.

-- Both queries must match in column count and data types
SELECT id1, name1 FROM u1
UNION
SELECT id1, name1 FROM u2;
-- Output column headers will inherit from u1 (id1, name1)

Key Takeaway: Set operations require matching column counts and compatible data types across all participating SELECT statements.

2. Combining Data with UNION vs UNION ALL

UNION and UNION ALL merge rows vertically from two datasets, differing strictly in how they handle duplicate rows across the combined output.

Mechanism: UNION merges records from both datasets and runs a deduplication step to retain only distinct rows. UNION ALL concatenates the datasets directly, retaining all occurrences of duplicate rows.
Under the Hood: UNION performs an implicit distinct sort or hash operation to strip duplicates, incurring higher computational overhead. UNION ALL skips this filtering phase, executing significantly faster.
Best Practice: Default to UNION ALL when rows are guaranteed to be unique or when preserving duplicate occurrences is required for business logic.

-- UNION: Combines and removes duplicates (e.g., rows 4 and 5 appear once)
SELECT id1, name1 FROM u1
UNION
SELECT id1, name1 FROM u2;

-- UNION ALL: Combines without deduplicating (e.g., rows 4 and 5 appear twice)
SELECT id1, name1 FROM u1
UNION ALL
SELECT id1, name1 FROM u2;

Key Takeaway: UNION ALL appends results without overhead, while UNION forces a distinct sort to eliminate duplicates.

3. Finding Overlaps with INTERSECT

INTERSECT isolates rows that are present in both input datasets simultaneously, acting as a strict mathematical intersection.

Mechanism: INTERSECT evaluates complete rows between the left and right queries and outputs only the rows that exist in both sets.
Deduplication: Similar to UNION, INTERSECT automatically removes duplicate occurrences from the intersection output, returning each matching row once.
Use Case: Use INTERSECT when you need to verify common records between two versions of a table or validate shared entities across staging and production tables.

-- INTERSECT: Returns only rows that exist in both u1 and u2 (rows 4 and 5)
SELECT id1, name1 FROM u1
INTERSECT
SELECT id1, name1 FROM u2;

Key Takeaway: INTERSECT returns unique rows common to both participating queries.

4. Finding Differences with Directional EXCEPT

EXCEPT implements set difference (A - B) by taking all unique rows from the first query and subtracting any rows found in the second query.

Execution Flow: The database takes the distinct rows from the first dataset and removes every record that appears anywhere in the second dataset.
Asymmetry Rule: EXCEPT is non-commutative; running u1 EXCEPT u2 computes (u1 - u2), whereas u2 EXCEPT u1 computes (u2 - u1). The two queries yield completely different result sets.
Under the Hood: EXCEPT deduplicates rows from the primary dataset before outputting rows that have no match in the secondary dataset.

-- u1 - u2: Returns rows unique to u1 (rows 1, 2, 3), removing overlap (4, 5)
SELECT id1, name1 FROM u1
EXCEPT
SELECT id1, name1 FROM u2;

-- u2 - u1: Returns rows unique to u2 (rows 6, 7, 8), removing overlap (4, 5)
SELECT id1, name1 FROM u2
EXCEPT
SELECT id1, name1 FROM u1;

Key Takeaway: EXCEPT outputs rows unique to the left dataset, making query ordering critical to the final output.

Topics Covered in EXCEPT, INTERSECT, UNION — For Diffs and Dedup

Set Operation Overview (0:00 - 0:45) — Introduction to sample tables and structural requirements for SQL set operations.
UNION Demonstration (0:46 - 1:20) — Executing UNION to combine two datasets while removing overlapping duplicate records.
UNION ALL Execution (1:21 - 1:40) — Running UNION ALL to demonstrate full dataset concatenation with duplicate rows preserved.
INTERSECT Operation (1:41 - 2:05) — Using INTERSECT to isolate rows that exist concurrently in both tables.
EXCEPT (A - B) (2:06 - 2:40) — Demonstrating set subtraction from u1 to u2 to extract unique left-table rows.
EXCEPT (B - A) (2:41 - 3:16) — Inverting the EXCEPT query order to extract rows unique to the right table.

SQL Cheat Sheet

UNION — Combines result sets and eliminates all duplicate rows

SELECT id1, name1 FROM u1
UNION
SELECT id1, name1 FROM u2;

UNION ALL — Combines result sets while retaining all duplicate rows

SELECT id1, name1 FROM u1
UNION ALL
SELECT id1, name1 FROM u2;

INTERSECT — Returns distinct rows shared across both query results

SELECT id1, name1 FROM u1
INTERSECT
SELECT id1, name1 FROM u2;

EXCEPT (A - B) — Returns distinct rows in left query not in right

SELECT id1, name1 FROM u1
EXCEPT
SELECT id1, name1 FROM u2;

EXCEPT (B - A) — Returns distinct rows in right query not in left

SELECT id1, name1 FROM u2
EXCEPT
SELECT id1, name1 FROM u1;

Comparison Table

Operator	Duplicates Handled	Order Dependent (Commutative)
UNION	Removes duplicates	No (Commutative)
UNION ALL	Preserves duplicates	No (Commutative)
INTERSECT	Removes duplicates	No (Commutative)
EXCEPT	Removes duplicates	Yes (Non-commutative)

Common Pitfalls

Mistake: Expecting EXCEPT to work symmetrically regardless of query order. Avoid: Position the baseline dataset first and the subtracted dataset second.
Mistake: Using UNION when duplicates are already impossible or acceptable. Avoid: Use UNION ALL to prevent expensive unnecessary distinct sorting operations.
Mistake: Combining queries with different column counts or incompatible datatypes. Avoid: Explicitly verify matching column counts and compatible data types positional alignment.

FAQs

Why does EXCEPT return different rows when changing table order? EXCEPT computes set difference (A - B). Inverting the order computes (B - A), isolating records unique to table B instead of table A.
Does INTERSECT preserve duplicate matching rows? No, INTERSECT returns only distinct rows that exist across both datasets, automatically stripping duplicate occurrences.
What happens if column names differ between the two SELECT queries? The query executes normally as long as the positional datatypes match; the final output column headers inherit names from the first SELECT query.

Common pitfalls

Mistake: Expecting EXCEPT to work symmetrically regardless of query order. Avoid: Position the baseline dataset first and the subtracted dataset second.
Mistake: Using UNION when duplicates are already impossible or acceptable. Avoid: Use UNION ALL to prevent expensive unnecessary distinct sorting operations.
Mistake: Combining queries with different column counts or incompatible datatypes. Avoid: Explicitly verify matching column counts and compatible data types positional alignment.

Hello, friends. Welcome to SQL Server tutorial. In the previous video, we created the sample tables. Now let's explore set operations. And we will experiment the set operations UNION, UNION ALL, INTERSECT, as well as EXCEPT. In the first video, we learned about the basics of set operations. Then in the second video, we created the sample tables, which is useful to experiment the set operations. Now we are at the final part of the three-video sub-series. And here we will perform the set operations. So, in the last video itself, we reviewed the data. So, here if you see, we make selection from a table u1. So, this is the first query, which returns two column, id1, name1. Then we retrieve id1 and name1 from table u2. Since the number of column is matching as well as their data type also matching, we can perform the set operation. Here, we are performing the set operation UNION. So, this will combine records from both the table and after combining, it eliminates the duplicate from the result set. Next, we use the same to experiment the UNION ALL set operation. Then we go ahead with the same set of query, and here we experiment the INTERSECT. And finally, our experiment goes how the EXCEPT is different. So, here, the EXCEPT is for a - b. That means here we use table 1, table 2, and we experiment 1 - 2. Then in the second example, we perform here 2 - 1. So, this will definitely show you the difference and how it works. Now let's go to the demo and experiment the set operations. So, in the last video, we retrieved content of both the table, right? Let's leave it like this and we will open a new query editor window. And we will place them side by side. This is not required. We will keep that in the left side pane. Here we just retrieved the content from both the table. So, in the second query window, we will skip this part. Now, we are applying the UNION operation on query 1 and query 2. Query 1 is nothing but content of table u1, this one I mean, and query 2 is content of table t2 or table u2. Here, if you see, the records from table 1 is retrieved, 1, 2, 3. And you can see that from table 1. And frame table 2, record 6 through 8, that's also there. But if you see 4 and 5, this exist between both the table. Right? So the highlighted one. But in the result, if you see, the duplicates are removed. 4, 5, 4, 5. So, it is picked from, you can, since the row is completely equal, and we can state that it picked the rows from table 1, and since the row is equal in table 2, it skipped 4, name4, 5, name5. And it picked the remaining. So that's the, it in the sense, the UNION operator. Right? Now let's experiment the second one. Here is the UNION ALL example, and UNION ALL, if you see, you will be seeing the duplicates. 4, name4, 5, name5, it will get repeated one more time. Why? Because it will simply return content of table 1, then content of table 2. So that means you will be seeing 4, 5 two times. Let's say, execute this. And if you see, the highlighted records are duplicates. The first two from table 1, and the second two is from table 2. I mean table u2. So that's the UNION ALL. Next, we will experiment INTERSECT. So now you know what is the intersection. Right? 4, name4, and from here, 4, name4, this is one intersect, 5, name5, 5, name5, this is one more intersect. Now if I execute, it will bring only those two record which is intersecting and it will avoid duplicate as well. So 4, name4, that's one intersection, 5, name5, that's one intersection. So there are two record that is intersecting between table u1 and table u2. So that's the use of INTERSECT operator here. Now, let us look at a - b. So table u1, we will consider that as a, table u2, we will consider that as b. a - b means, so this is the complete a - b. - b means here 4, 5 is there, right? So skip this 4 and skip this 5, because that exist here. So a - b means this is the record. a, negate the content that is intersecting with the b. So the content that intersect with b is record 4 and 5. So now let's execute this and you will be seeing record that exist only on table a or table u1. And if you see, the records are 1, 2, 3. Now we can experiment the same with b - a. Here, if you see, the same EXCEPT we are using, but this time we are using table u2 first. That means the operation is b - a. Or we can read it like this: take all the records from u2 and negate, EXCEPT means, except the records from u1. So, there are some u1 records that is intersecting on u2, remove that. Except, take all except u1. So, take all means take all from u2, and except u1 records here in u2 that get mixed with u2, right? So, negate that. That's what here we are stating. And if I execute this, now you will be seeing 6, 7, 8, removing the intersection of 4 and 5. So, 6, 7, 8, name6, name7, name8. So that's all here. So, now we experimented all, UNION, UNION ALL, INTERSECT, as well as EXCEPT. When we experimented the EXCEPT, we uh experimented a - b as well as b - a operation. And if you see, the result is different for these two queries. That's all here in this video. Thank you for watching. Bye.

Frequently asked questions

Why does EXCEPT return different rows when changing table order?

EXCEPT computes set difference (A - B). Inverting the order computes (B - A), isolating records unique to table B instead of table A.

Does INTERSECT preserve duplicate matching rows?

No, INTERSECT returns only distinct rows that exist across both datasets, automatically stripping duplicate occurrences.

What happens if column names differ between the two SELECT queries?

The query executes normally as long as the positional datatypes match; the final output column headers inherit names from the first SELECT query.

How was this lesson?

Your feedback helps us refine explanations and catch bugs.