Combining Results with SQL Set Operations: UNION, UNION ALL, INTERSECT, and EXCEPT

Introduction

Imagine you're a data analyst at a retail company. Your sales data lives in two places: a current_orders table for this year's transactions and an archived_orders table for everything from prior years. Your manager needs a single report covering all orders. You could export both tables to a spreadsheet and paste them together — or you could let SQL do it in one query. That's exactly the kind of problem set operations were built to solve.

SQL set operations let you combine the results of two or more separate SELECT statements into a single result. Instead of linking tables horizontally (the way SQL JOINs do), set operations stack results vertically — they operate on rows from multiple queries and merge them according to rules you define. Understanding the difference between these two approaches is one of those foundational insights that separates a beginner from someone who can actually design queries for real data challenges.

By the end of this lesson, you'll be comfortable combining query results using all four set operations in SQL. You'll understand not just the syntax, but why each operator behaves the way it does — so you can pick the right tool without guessing.

What you'll learn:

• How UNION and UNION ALL stack rows from multiple queries, and when to use each one
• How INTERSECT finds rows that appear in both queries
• How EXCEPT (or MINUS in Oracle) finds rows that appear in one query but not another
• The rules all set operations must follow (column compatibility)
• How to sort and extend set operation results

---

Prerequisites

Before diving in, you should be comfortable writing basic SELECT queries with WHERE clauses. If you need a refresher, start with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries. You don't need any experience with joins or subqueries.

---

The Golden Rule: Column Compatibility

Before you can combine two queries with a set operation, both queries must produce result sets that are structurally compatible. This means:

1. Same number of columns — if the first SELECT returns three columns, the second one must too.
2. Compatible data types — column positions must match up in type. A VARCHAR column in position one of the first query should match a text column in position one of the second.
3. Column names come from the first query — whatever you name the columns in the first SELECT is what the final result will use.

Think of it like stacking shipping boxes: both boxes need to be the same width and depth before you can stack them neatly. If one box is wider, they won't stack cleanly.

Let's set up a scenario we'll use throughout this lesson.

---

Sample Data Setup

Suppose you work for a staffing company. You have two tables tracking employees:

-- Full-time employees
CREATE TABLE full_time_employees (
    employee_id   INT,
    full_name     VARCHAR(100),
    department    VARCHAR(50),
    hire_date     DATE
);

INSERT INTO full_time_employees VALUES
(1,  'Maria Santos',   'Engineering',  '2019-03-15'),
(2,  'James Okafor',   'Marketing',    '2020-07-01'),
(3,  'Priya Nair',     'Engineering',  '2021-01-10'),
(4,  'Tom Brennan',    'Finance',      '2018-11-20');

-- Contractors
CREATE TABLE contractors (
    contractor_id INT,
    full_name     VARCHAR(100),
    department    VARCHAR(50),
    start_date    DATE
);

INSERT INTO contractors VALUES
(101, 'Maria Santos',   'Engineering',  '2019-03-15'),
(102, 'Sandra Diaz',   'Marketing',    '2022-05-18'),
(103, 'Liu Wei',       'Engineering',  '2023-02-01');

Notice that Maria Santos appears in both tables — she may have converted from contractor to full-time, or the data has some duplication. This kind of overlap is exactly what makes set operations so useful.

---

UNION: Combining Results Without Duplicates

UNION takes the results of two queries and stacks them into one result, automatically removing duplicate rows. A duplicate, in this context, means a row where every column value is identical to another row in the combined result.

SELECT full_name, department
FROM full_time_employees

UNION

SELECT full_name, department
FROM contractors;

Result:

full_name	department
Maria Santos	Engineering
James Okafor	Marketing
Priya Nair	Engineering
Tom Brennan	Finance
Sandra Diaz	Marketing
Liu Wei	Engineering

Maria Santos appears only once, even though she exists in both tables. UNION detected that the row ('Maria Santos', 'Engineering') was identical across both queries and kept only one copy.

Why Does UNION Deduplicate?

The concept comes directly from set theory in mathematics. In a mathematical set, each element appears exactly once. UNION in SQL follows the same rule: the union of two sets contains every unique element from both. If you've studied Advanced SQL Filtering and Sorting, you may have encountered SELECT DISTINCT — UNION applies similar deduplication logic to the combined output.

The cost of that deduplication is real: the database has to sort or hash all the rows to identify and remove duplicates. For large datasets, that's extra processing time. Which brings us to the next operator.

---

UNION ALL: Combining Results and Keeping Everything

UNION ALL works identically to UNION, except it skips the deduplication step. Every row from both queries appears in the result, duplicates included.

SELECT full_name, department
FROM full_time_employees

UNION ALL

SELECT full_name, department
FROM contractors;

Result:

full_name	department
Maria Santos	Engineering
James Okafor	Marketing
Priya Nair	Engineering
Tom Brennan	Finance
Maria Santos	Engineering
Sandra Diaz	Marketing
Liu Wei	Engineering

Now Maria Santos appears twice — once from each table. All seven rows are present.

UNION vs. UNION ALL: Which Should You Use?

This is one of the most common beginner decisions to get wrong, so let's be direct about it:

• Use UNION ALL by default when you know there are no duplicates (or when you want to keep them).
• Use UNION only when you genuinely need to eliminate duplicates.

Why? Because UNION ALL is always faster. Deduplication requires sorting or hashing every row in the combined result, which adds computational cost. If you're stacking sales data from January and February knowing there's no overlap, running UNION is doing unnecessary work.

---

Adding Labels to Track the Source

One practical trick: add a literal string column to each query so you can tell where each row came from in the combined result.

SELECT full_name, department, 'Full-Time' AS employment_type
FROM full_time_employees

UNION ALL

SELECT full_name, department, 'Contractor'
FROM contractors;

Result:

full_name	department	employment_type
Maria Santos	Engineering	Full-Time
James Okafor	Marketing	Full-Time
Priya Nair	Engineering	Full-Time
Tom Brennan	Finance	Full-Time
Maria Santos	Engineering	Contractor
Sandra Diaz	Marketing	Contractor
Liu Wei	Engineering	Contractor

The literal 'Full-Time' and 'Contractor' strings satisfy the column compatibility rule — both queries now return three columns of compatible types. This pattern is especially useful for audit trails and reconciliation reports.

---

Sorting a Set Operation's Results

You can only use one ORDER BY clause per set operation, and it goes at the very end of the entire statement — after all the SELECT blocks.

SELECT full_name, department, 'Full-Time' AS employment_type
FROM full_time_employees

UNION ALL

SELECT full_name, department, 'Contractor'
FROM contractors

ORDER BY department, full_name;

The ORDER BY applies to the final combined result. You can reference columns by their name (from the first query) or by position number.

---

INTERSECT: Finding What's in Both

INTERSECT returns only the rows that appear in both queries. Think of it as asking: "What do these two result sets have in common?"

SELECT full_name, department
FROM full_time_employees

INTERSECT

SELECT full_name, department
FROM contractors;

Result:

full_name	department
Maria Santos	Engineering

Only Maria Santos is in both tables with the same department, so she's the only row returned. This is a powerful tool for reconciliation — for instance, finding customers who appear in both your CRM and your billing system.

Practical Use Cases for INTERSECT

• Finding products that exist in both your warehouse inventory and your online catalog
• Identifying customers who placed orders in both Q1 and Q2 (two separate subqueries, each returning customer IDs)
• Spotting email addresses that are on both a marketing list and an unsubscribe list

---

EXCEPT: Finding What's in One but Not the Other

EXCEPT (called MINUS in Oracle databases) returns rows from the first query that do not appear in the second query. The order matters here — EXCEPT is not symmetric.

-- Full-time employees who are NOT contractors
SELECT full_name, department
FROM full_time_employees

EXCEPT

SELECT full_name, department
FROM contractors;

Result:

full_name	department
James Okafor	Marketing
Priya Nair	Engineering
Tom Brennan	Finance

Maria Santos is excluded because she appears in both. The three remaining full-time employees appear only in full_time_employees, so they pass through.

Now flip the order:

-- Contractors who are NOT full-time employees
SELECT full_name, department
FROM contractors

EXCEPT

SELECT full_name, department
FROM full_time_employees;

Result:

full_name	department
Sandra Diaz	Marketing
Liu Wei	Engineering

Different result entirely. Always read EXCEPT as: "Give me rows from the first query, minus anything that also appears in the second query."

EXCEPT vs. NOT EXISTS and Anti-Joins

EXCEPT does the same job as an anti-join or a NOT EXISTS subquery in many situations. For example, the query above could also be written as:

SELECT full_name, department
FROM full_time_employees fte
WHERE NOT EXISTS (
    SELECT 1
    FROM contractors c
    WHERE c.full_name = fte.full_name
      AND c.department = fte.department
);

The EXCEPT version is often cleaner to read. However, NOT EXISTS gives you more flexibility — for instance, you can join on specific key columns rather than comparing entire rows. You can read more about that pattern in Mastering SQL EXISTS and NOT EXISTS.

---

Combining More Than Two Queries

You're not limited to two queries per set operation. You can chain multiple SELECT blocks together:

SELECT full_name, department FROM full_time_employees
UNION ALL
SELECT full_name, department FROM contractors
UNION ALL
SELECT full_name, department FROM interns;

Each operator is evaluated left to right unless you use parentheses to control order. When mixing different operators (say, UNION and EXCEPT in the same statement), parentheses become important:

(
  SELECT full_name, department FROM full_time_employees
  UNION ALL
  SELECT full_name, department FROM contractors
)
EXCEPT
SELECT full_name, department FROM terminated_employees;

This first builds the combined employee list, then removes anyone who's been terminated. Without parentheses, the behavior can be unpredictable depending on how your database parses operator precedence.

---

Hands-On Exercise

Work through these exercises using the sample tables from earlier in this lesson. Create them in any SQL environment you have access to (PostgreSQL, MySQL, SQLite, or an online tool like DB Fiddle or SQLiteOnline).

Exercise 1: Write a query using UNION ALL that lists all people (full-time and contractors) with a column called source that shows either 'FTE' or 'CTR'. Sort the result by full name.

Exercise 2: Use INTERSECT to find any names that appear in both tables. What does the result tell you about your data?

Exercise 3: Write an EXCEPT query that returns contractors who have not been hired as full-time employees. Then reverse the query — find full-time employees who were never contractors. Compare the two results.

Exercise 4 (challenge): Add a third query to your UNION ALL from Exercise 1 that pulls from a fictional interns table. Even if you don't create the table, write out the SQL structure — this will test whether you understand the column compatibility rule.

---

Common Mistakes & Troubleshooting

Mistake 1: Different Column Counts

-- This will fail
SELECT full_name, department, hire_date
FROM full_time_employees

UNION

SELECT full_name, department  -- only two columns!
FROM contractors;

Fix: Make both queries return the same number of columns. If one table doesn't have a matching column, use NULL as a placeholder:

SELECT full_name, department, hire_date
FROM full_time_employees

UNION

SELECT full_name, department, NULL AS hire_date
FROM contractors;

Mistake 2: Using UNION When You Should Use JOIN

Set operations combine rows vertically. JOINs combine data horizontally (adding columns). If you want to add information from another table — like looking up a department manager's name — that's a JOIN, not a UNION. New learners sometimes confuse these two because both "combine tables," but they're doing fundamentally different things.

Mistake 3: Forgetting That EXCEPT Is Ordered

EXCEPT returns rows from the first query that aren't in the second. Swapping the order completely changes the result. Always read your query out loud: "Give me everything from query A, except what also appears in query B."

Mistake 4: Using ORDER BY in the Wrong Place

Putting ORDER BY inside one of the individual SELECT blocks in a set operation will either fail or produce unexpected results. Always place it at the very end of the complete statement.

Mistake 5: Expecting UNION to Match on Keys

UNION deduplication compares complete rows. If Maria Santos has a slightly different department value in the two tables ('Eng' vs. 'Engineering'), she'll appear twice. UNION has no way to know those should be the same person. This is a data quality issue, not a SQL problem — but it's a common source of confusion.

---

Summary & Next Steps

You've now got a solid working understanding of all four SQL set operations:

• UNION — combines two query results, removing duplicates. Use when you need a clean, deduplicated list across multiple sources.
• UNION ALL — combines two query results, keeping everything. Faster than UNION; use this by default when duplicates aren't a concern.
• INTERSECT — returns only rows found in both queries. Perfect for finding overlap between datasets.
• EXCEPT — returns rows from the first query not found in the second. Essential for gap analysis and reconciliation.

The golden rule underlining all of them: your queries must return the same number of columns, with compatible data types, in the same order.

Set operations are a powerful part of the SQL toolkit precisely because they let you think in terms of sets of data rather than individual rows. That's a meaningful mental shift. As your queries grow more complex, you'll often find yourself combining set operations with subqueries and CTEs to break large data problems into readable, manageable steps.

For a deeper dive into advanced applications of these operators — including using them for complex deduplication and multi-source data reconciliation — check out Mastering SQL Set Operations: UNION, INTERSECT, and EXCEPT for Complex Data Reconciliation and Deduplication.

From here, you might also explore grouping and summarizing your combined results — once you've stacked data from multiple sources, applying GROUP BY and aggregate functions on the combined output is a natural next step.