SQL UNION vs UNION ALL: Key Differences Explained

Quick Overview

• UNION removes duplicate rows from combined results; UNION ALL keeps every row, including duplicates.
• UNION ALL is faster since it skips the sorting/hashing step needed to check for duplicates.
• Both require the same column count, order, and compatible data types across all SELECT statements.
• Use UNION for unique lists (customers, products); use UNION ALL for totals, logs, and transaction counts.
• Defaulting to UNION when you don't need deduplication can silently understate counts, revenue, or activity metrics; use UNION ALL unless duplicates are a real concern.

This blog breaks down everything you need to know about SQL UNION vs UNION ALL in plain language. You will learn what each operator does, how they handle duplicates and NULL values, how they perform on large datasets, and when to use one over the other.

If you're curious about how technologies like SQL, Python, and data visualization power real-world analytics, upGrad's Data Science courses can help you build these skills hands-on, from data analysis to machine learning, and open doors across industries that run on data.

SQL UNION vs UNION ALL: The Main Difference

The core difference in SQL UNION vs UNION ALL comes down to one thing: duplicates.

• UNION combines the result sets of two or more SELECT statements and removes duplicate rows automatically. It only keeps unique rows in the final output.
• UNION ALL also combines result sets from multiple SELECT statements, but it keeps every row, including duplicates. Nothing gets filtered out.

Here is the simplest way to picture it. Imagine you have two lists of customer names, and some names appear on both lists. UNION would give you one clean list with no name repeated. UNION ALL would give you both lists stacked together, repeats and all.

This single difference drives almost every other distinction between the two operators, including speed, memory usage, and how they affect your data analysis.

Feature	UNION	UNION ALL
Removes duplicates	Yes	No
Speed	Slower	Faster
Memory usage	Higher	Lower
Use case	Need unique results	Need all records, including duplicates

Both operators exist in nearly every major database system, including SQL Server, MySQL, PostgreSQL, Oracle, and Spark SQL. The syntax stays almost identical across platforms, which makes this concept easy to carry from one database to another.

Before we go deeper into performance and use cases, it helps to understand each operator on its own. The next two sections cover UNION and UNION ALL individually, so you have a solid base before comparing them side by side.

Also Read: SQL Tutorials: Complete Learning Guide

What is UNION in SQL?

UNION is a set operator in SQL that combines the results of two or more SELECT statements into a single result set. It automatically removes any duplicate rows, so every row in the final output is unique.

Think of UNION as a merge-and-clean operation. It runs each SELECT statement, stacks the results together, then scans the combined set and drops any row that appears more than once.

Basic Syntax

SELECT column1, column2 FROM table1 
UNION 
SELECT column1, column2 FROM table2;

Fundamental Rules for UNION

A few rules apply every time you use UNION:

• Each SELECT statement must return the same number of columns.
• Columns must be in the same order across all SELECT statements.
• Corresponding columns must have compatible data types.
• Column names in the final result come from the first SELECT statement.

Example

Suppose you have two tables, online_customers and store_customers, and some people show up in both.

SELECT customer_name FROM online_customers 
UNION 
SELECT customer_name FROM store_customers; 

If "Rahul Mehta" exists in both tables, UNION returns his name only once. This makes UNION useful whenever you need a clean, deduplicated list, such as building a master list of unique customers, products, or locations from multiple sources.

UNION is common in reporting scenarios where duplicate entries would distort the picture. If you are merging customer emails from a website and an app database, you probably do not want the same email showing up twice in your final list. That is the exact situation UNION is built for.

Keep in mind that this deduplication step means UNION has to do extra work internally. It needs to sort or hash the combined data to identify duplicates before returning results. This becomes important later when we compare performance between SQL union vs union all.

Also Read: SQL For Beginners: Essential Queries, Joins, Indexing & Optimization Tips

What is UNION ALL in SQL?

UNION ALL is a set operator in SQL that combines the results of two or more SELECT statements into a single result set, but unlike UNION, it does not remove duplicates. Every row from every SELECT statement appears in the final output, even if some rows are identical.

This makes UNION ALL a simpler and more direct operation. It does not need to compare rows against each other or filter anything out. It just appends the results together.

Basic Syntax of UNION ALL in SQL

SELECT column1, column2 FROM table1 
UNION ALL 
SELECT column1, column2 FROM table2; 

The same fundamental rules apply here as with UNION. Column count, order, and compatible data types are still required across all SELECT statements.

Example

Using the same customer tables from before:

SELECT customer_name FROM online_customers 
UNION ALL 
SELECT customer_name FROM store_customers; 

If "Rahul Mehta" exists in both tables, UNION ALL returns his name twice. Nothing gets filtered.

This SQL UNION vs UNION ALL example shows exactly why UNION ALL matters for analytics. If you are counting total transactions, total orders, or total events across multiple tables, you usually want every row counted, including repeats. Removing duplicates in that case would give you a wrong, lower number.

UNION ALL is the better default in most performance-sensitive scenarios because it skips the deduplication step entirely. Database engines process it faster since there is no sorting or comparison overhead.

If you already know your data has no duplicates, or you actually want to keep every row, UNION ALL is almost always the right choice over UNION.

Also Read: Understanding the Types of SQL Operators: Practical Examples and Best Practices

Similarities Between UNION and UNION ALL

Before looking deeper at how SQL union vs union all differ, it helps to know what they have in common. Both operators serve the same basic purpose, just with one key behavioral difference in how they treat duplicate rows.

Here is what stays consistent across both:

• Both combine results from two or more SELECT statements into a single result set.
• Both require the same number of columns in each SELECT statement.
• Both require columns to appear in the same order.
• Both require compatible data types between corresponding columns.
• Both can be used with WHERE, ORDER BY, and subqueries.
• Both work across major databases including SQL Server, MySQL, PostgreSQL, Oracle, and Spark SQL.
• Both take the column names from the first SELECT statement in the result set.

In other words, structurally, UNION and UNION ALL are identical operators. The only functional difference is that UNION checks for and removes duplicate rows, while UNION ALL does not.

This is worth remembering because it means switching between the two in your query is usually a one-word change. If you write a query using UNION and later realize you actually want to keep duplicates, you can simply add the word ALL without restructuring anything else.

It also means both operators carry the same column-matching restrictions. If your query fails with UNION due to a column mismatch, the exact same query will fail with UNION ALL for the same reason. The error has nothing to do with duplicates and everything to do with structure.

Understanding this overlap makes the differences easier to grasp, because you are only tracking one variable: how duplicates are handled. Everything else about syntax, structure, and requirements stays the same. With that foundation set, let's look closely at where the two operators actually diverge, starting with duplicate handling and NULL values.

upGrad's Executive Post Graduate Certificate Programme in Data Science & AI from IIITB helps you build these in-demand skills, from data analytics to machine learning and AI, backed by IIIT Bangalore's academic rigor and industry-relevant curriculum. Explore the programme and take the next step toward a career in Data Science & AI.

Key Differences Between UNION and UNION ALL 

Now that you know what each operator does individually, let's line up the differences directly. This is the section most people are really searching for when they type SQL union vs union all into Google.

1. Duplicate Handling

This is the defining difference. UNION scans the combined result set and removes any row that matches another row exactly across all selected columns. UNION ALL skips this step completely and returns every row as is.

A row only counts as a duplicate in UNION if every column value matches. If even one column differs, the rows are treated as unique and both are kept.

2. How NULL Values Are Handled

A common point of confusion is how UNION treats NULL values when checking for duplicates. In SQL, NULL is generally not equal to NULL in comparisons. But UNION treats two rows with NULL in the same column position as duplicates for deduplication purposes, as long as every other column also matches.

So, if two rows are identical and both have NULL in the same column, UNION will still remove one of them. This is a special case built into how the set operation works, different from how NULL behaves in a WHERE clause comparison.

3. Performance Differences

UNION ALL is faster in almost every case. Since it does not need to check for or remove duplicates, the database engine avoids the extra sorting or hashing step that UNION requires.

UNION, on the other hand, needs to process the entire combined result set to identify and remove duplicates. On large datasets, this extra step can add noticeable time and resource usage.

4. Syntax Comparison

The syntax difference is just one word:

-- UNION 
SELECT city FROM warehouse_a 
UNION 
SELECT city FROM warehouse_b; 
 
-- UNION ALL 
SELECT city FROM warehouse_a 
UNION ALL 
SELECT city FROM warehouse_b; 

Quick Comparison Table Between UNION and UNION ALL

Aspect	UNION	UNION ALL
Duplicate rows	Removed	Kept
NULL handling in duplicates	Treated as matching	Treated as matching (not filtered anyway)
Speed on large data	Slower	Faster
Resource usage	Higher (sorting/hashing)	Lower
Best for	Unique value lists	Full row counts, logs, transactions

Understanding these four points, duplicates, NULLs, performance, and syntax, covers most of what people need when comparing SQL union vs union all in SQL server, MySQL, or any other platform.

Also Read: Top SQL Queries in Python Every Python Developer Should Know 

Rules and Requirements for Using UNION and UNION ALL

Both operators come with structural rules you need to follow, or your query will fail. These rules apply the same way whether you use UNION or UNION ALL.

1. Combining Multiple Tables

You are not limited to two SELECT statements. You can chain as many as you need:

SELECT product_name FROM store_1 
UNION ALL 
SELECT product_name FROM store_2 
UNION ALL 
SELECT product_name FROM store_3; 

Each additional SELECT statement must follow the same column rules as the first two.

2. Column Rules

• The number of columns must match across every SELECT statement.
• Columns must appear in the same order.
• Data types in corresponding columns must be compatible, though they do not need to be identical in every database.

If you try to combine a query returning 3 columns with one returning 4 columns, you will get an error. This is one of the most common mistakes beginners run into.

3. Sorting Results with ORDER BY

You can only use ORDER BY once, and it must go at the very end of the entire combined query, not after each individual SELECT statement.

SELECT employee_name, department FROM team_a 
UNION ALL 
SELECT employee_name, department FROM team_b 
ORDER BY employee_name; 

4. Filtering with WHERE Clause

WHERE clauses work inside each individual SELECT statement, before the union happens. This lets you filter each source table differently before combining them.

SELECT order_id FROM orders_2024 WHERE status = 'completed' 
UNION ALL 
SELECT order_id FROM orders_2025 WHERE status = 'completed'; 

5. Using UNION with Subqueries

You can use UNION or UNION ALL inside a subquery, or wrap a UNION query as a subquery itself. This is common when you need to combine data before applying additional filtering or aggregation.

SELECT region, SUM(sales) AS total_sales 
FROM ( 
    SELECT region, sales FROM sales_north 
    UNION ALL 
    SELECT region, sales FROM sales_south 
) combined_sales 
GROUP BY region; 

This pattern is especially useful in SQL union vs union all in SQL server scenarios where you need to merge partitioned tables before running aggregate calculations.

Also Read: Most Asked Oracle Interview Questions and Answers - For Freshers and Experienced

Execution Plan for UNION vs UNION ALL Query

If you look at the execution plan for a UNION query versus a UNION ALL query, the difference becomes very visible. This is one of the clearest ways to understand why SQL union vs union all matters beyond just the final output.

When you run UNION, the database engine typically adds a sort or hash step after combining the result sets. This step scans through all the rows to identify and remove duplicates. On small tables, this barely registers. On large tables with millions of rows, this step can become one of the most expensive parts of the entire query.

UNION ALL skips this step entirely. The execution plan simply shows a concatenation of the result sets, with no extra sorting or comparison operation. This is why UNION ALL consistently uses less CPU and memory than UNION for the same data.

Here is a general breakdown of what happens internally:

Step	UNION	UNION ALL
Run each SELECT statement	Yes	Yes
Combine results	Yes	Yes
Sort or hash for duplicate check	Yes	No
Remove duplicate rows	Yes	No
Return final result	Yes	Yes

When comparing SQL Server UNION vs. UNION ALL directly, you can view this cost difference by checking the actual execution plan.

A UNION query usually shows a "Distinct Sort" or "Hash Match" operator that does not appear in the UNION ALL version of the same query. This single operator is often responsible for most of the performance gap.

The practical takeaway is simple: if you already know your combined data will not contain duplicates, or if duplicates do not matter for your use case, always use UNION ALL. You get the same result faster, without wasting resources on a deduplication step you did not need in the first place.

This becomes especially important in Spark SQL and other distributed systems, which we will touch on later, since the deduplication step in UNION requires shuffling data across nodes, which is expensive at scale.

Also Read: Top 27 SQL Projects in 2026 With Source Code: For All Levels

How UNION vs UNION ALL Affects Analytics and Metrics

This is where getting SQL union vs union all wrong can cause real damage. If you are building reports or calculating business metrics, this choice is not just a performance question. It can change your actual numbers.

Impact on Counts

Imagine you are counting total customer interactions from two log tables, one for app events and one for website events. If you use UNION instead of UNION ALL, and a row happens to be identical across both columns you selected, that row gets removed.

Your interaction count comes out lower than reality, even though nothing was technically wrong with your data.

This mistake is especially easy to make when your SELECT statement only pulls a few columns. Fewer columns means a higher chance that two genuinely different events look identical to the database, and get treated as duplicates.

Impact on Revenue Calculations

Revenue reporting is even more sensitive. If two separate transactions happen to have the same customer ID, same amount, and same date, UNION would treat them as duplicates and drop one.

That means your reported revenue would be understated, potentially by a significant amount if this happens across many rows.

For any query involving SUM(), COUNT(), or AVG() across combined tables, UNION ALL is almost always the safer choice, unless you specifically want deduplicated values.

Impact on Retention and Activity Metrics

Retention metrics often rely on counting user activity across multiple time periods or multiple event tables. Using UNION here can silently merge two separate but identical-looking activity records into one, which understates how active your users actually are.

The safest rule of thumb: use UNION ALL by default for any metric calculation, and only switch to UNION when you specifically need a deduplicated list, such as a unique customer list or unique product list. Getting this wrong does not throw an error. It just quietly gives you the wrong number, which is often worse.

Also Read: SQL for Data Science: A Complete Guide for Beginners

When to Use UNION vs UNION ALL

Choosing between UNION and UNION ALL comes down to one question: do you need unique rows, or do you need every row.

When to Choose UNION

Use UNION when:

• You are building a list of unique values, like unique customer names or unique product SKUs.
• Duplicate rows would create incorrect or misleading results.
• You are merging reference data or lookup tables where repeats do not add value.
• Data accuracy matters more than query speed for this specific use case.

This decision looks slightly different depending on your platform. In sql server union vs union all scenarios, teams often lean on UNION ALL for reporting pipelines where speed matters. In Spark SQL union vs union all situations, the choice matters even more, since UNION triggers a shuffle across distributed nodes that can noticeably slow down a job.

When to Choose UNION ALL

Use UNION ALL when:

• You need every row, including duplicates, such as transaction logs or event records.
• You are calculating totals, counts, or sums where every row should count.
• You already know there are no duplicates between your source tables.
• Query performance matters and you want to avoid unnecessary processing.

Also Read: 52+ PL SQL Interview Questions Every Developer Should Know

Choosing for Large Datasets

On large datasets, this decision matters even more. UNION ALL scales better because it avoids the sorting and comparison overhead that UNION requires.

If you are working with millions of rows across multiple large tables, that overhead can turn a fast query into a slow one.

A practical approach many teams use: start with UNION ALL by default, and only add the deduplication step, either through UNION or a separate DISTINCT clause, if you actually confirm duplicates are a problem.

This way, you are not paying a performance cost for a scenario that might not even exist in your data.

A useful SQL union vs union all example to try on your own data: if you are unsure whether duplicates exist, run a quick UNION ALL query first and check the row count.

Compare it to a UNION version of the same query. If the counts match, you do not have duplicates, and UNION ALL is safe to use going forward.

Also Read: Is SQL Hard to Learn? Challenges, Tips, and Career Insights

Common Errors and How to Fix Them

Both UNION and UNION ALL can throw errors if the underlying queries are not structured correctly. Here are the most common issues and how to resolve them.

Mismatched Column Count Error

This happens when your SELECT statements return a different number of columns.

-- This will fail 
SELECT name, email FROM customers 
UNION ALL 
SELECT name FROM leads; 

Fix: Make sure both SELECT statements return the same number of columns. If one table is missing a column, you can add a placeholder.

SELECT name, email FROM customers 
UNION ALL 
SELECT name, NULL AS email FROM leads; 

Unexpected Duplicate Rows with UNION ALL

If you are seeing duplicate rows and did not expect them, remember that UNION ALL never removes duplicates by design. If you need unique rows, switch to UNION, or wrap your query with a DISTINCT clause if you need more control.

SELECT DISTINCT * FROM ( 
    SELECT customer_name FROM online_customers 
    UNION ALL 
    SELECT customer_name FROM store_customers 
) combined; 

Slow Query Performance

If your UNION query feels slow, check whether you actually need deduplication. Switching to UNION ALL, if duplicates are not a concern, often resolves the slowdown immediately. You can also check your execution plan to confirm whether the sort or hash step is the bottleneck. 

Indexing the columns used in your SELECT statements can also help both UNION and UNION ALL run faster, since the database can retrieve matching rows more efficiently before combining them. This applies to traditional databases as much as it does to spark sql union vs union all workloads, where partition pruning plays a similar role. 

Common Mistakes to Avoid

Anyone learning SQL UNION vs. UNION ALL eventually runs into the same handful of mistakes. Here are the ones that come up again and again:

• Using UNION out of habit without checking if duplicates are actually a concern, which slows down queries unnecessarily.
• Forgetting that column order matters, not just column count, which can silently mix up data between mismatched columns.
• Assuming ORDER BY works after each SELECT statement instead of at the end of the full combined query.
• Not accounting for NULL values when predicting which rows will be treated as duplicates.
• Mixing incompatible data types across corresponding columns, which some databases allow with implicit conversion and others reject outright.
• Using UNION ALL for a report that specifically needs unique values, leading to inflated counts.

Most of these mistakes are easy to catch once you know to look for them. The biggest one, by far, is defaulting to UNION without thinking about whether you actually need deduplication. That single habit is responsible for a large share of unnecessary slow queries in production systems.

Best Practices for Using UNION and UNION ALL

Keep these practices in mind whenever you are combining result sets:

• Default to UNION ALL unless you specifically need unique rows.
• Check your data for actual duplicates before assuming you need UNION.
• Keep column names consistent across SELECT statements for readability, even though only the first SELECT statement's names are used.
• Use table aliases or a source column to identify which table each row came from, especially when debugging combined queries.
• Test both UNION and UNION ALL on a sample dataset to compare row counts before finalizing your query.
• Review the execution plan on large queries to confirm where the performance cost is coming from.
• Document why you chose UNION over UNION ALL in complex queries, so future readers understand the intent.

These practices apply whether you are working in SQL Server UNION vs. UNION ALL scenarios, MySQL, PostgreSQL, or Spark SQL. The logic stays consistent across platforms, even if small syntax details shift.

UNION and UNION ALL vs Related Operators

UNION vs JOIN

UNION and JOIN solve different problems, even though both combine data from multiple sources. JOIN combines columns from two tables based on a matching condition, producing wider rows. UNION combines rows from two tables with the same columns, producing a longer result set.

If you need to add more columns, use JOIN. If you need to stack similar rows together, use UNION or UNION ALL. This distinction holds true whether you are looking at union vs union all in SQL server or any other relational database.

UNION ALL vs INTERSECT vs EXCEPT

These three operators all work with two result sets, but they answer different questions:

Operator	What it returns
UNION ALL	All rows from both result sets, including duplicates
INTERSECT	Only rows that appear in both result sets
EXCEPT (or MINUS)	Rows that appear in the first result set but not the second

If you are trying to find customers who appear in both your email list and your purchase list, INTERSECT is the right tool, not UNION.

If you want customers who signed up but never purchased, EXCEPT is what you need. UNION and UNION ALL are specifically for combining and stacking, not comparing sets against each other.

Example: UNION vs UNION ALL in Practice

Let's walk through a complete sql union vs union all example using two small tables.

Table: north_sales

product	amount
Laptop	50000
Mouse	500

Table: south_sales

product	amount
Laptop	50000
Keyboard	1500

Using UNION:

SELECT product, amount FROM north_sales 
UNION 
SELECT product, amount FROM south_sales; 

Result:

product	amount
Laptop	50000
Mouse	500
Keyboard	1500

Notice "Laptop, 50000" appears only once, even though it exists in both tables.

Using UNION ALL

SELECT product, amount FROM north_sales 
UNION ALL 
SELECT product, amount FROM south_sales; 

Result:

product	amount
Laptop	50000
Mouse	500
Laptop	50000
Keyboard	1500

Here, "Laptop, 50000" appears twice, once from each table.

If you were calculating total sales revenue, UNION ALL gives you the correct total. UNION would understate it by leaving out one Laptop sale, since it looks identical to the row in the other table.

This same logic applies in Spark SQL UNION vs. UNION ALL scenarios, where you are often combining large distributed datasets, and Spark SQL supports both operators with the same behavior described here, though UNION in Spark SQL can be more resource-intensive due to the distributed shuffle required to check for duplicates across partitions.

Also Read: Attributes in DBMS: Types & Their Role in Databases

Conclusion

The decision between choosing SQL UNION and UNION ALL comes down to the question of whether you want unique rows or all rows. Keep the column rules, NULL behavior, and execution plan differences in mind, and you will avoid the most common mistakes people run into with these two operators, whether you are working in SQL Server, MySQL, PostgreSQL, or Spark SQL.

Want personalized guidance on Data Science and upskilling? Speak with an expert for a free 1:1 counselling session today.