UNION vs UNION ALL in SQL: A Comprehensive Guide

Tim S

Tim has worked as FAANG as a data analyst. He's been in the industry for 5+ years and writes for Big Tech Interviews on the weekends. He is also a writer for a SaaS management platform. He calls Denver, CO his home.

If you’re preparing for a technical interview or just looking to sharpen your SQL skills, understanding the difference between UNION and UNION ALL is essential. These two commands are key tools for combining data from multiple queries—but how they handle duplicates can impact both your results and performance.

In this guide, we’ll break down what UNION and UNION ALL do, walk through syntax and real-world examples, and highlight when to use each. You’ll also learn how platforms like Oracle handle these operations, along with best practices for writing efficient queries.

By the end, you’ll know exactly when to use UNION vs. UNION ALL and how to apply each in a way that makes your queries faster and more effective.

Let’s dive in.

What is the Difference Between UNION and UNION ALL?

The primary difference between UNION and UNION ALL (such as SQL Server UNION vs UNION ALL operations) is how they handle duplicate rows. UNION combines datasets from multiple SELECT statements and removes duplicate records, ensuring that the result set only contains unique entries.

On the other hand, UNION ALL combines datasets and includes all records, even if some are duplicates.

This distinction affects both the output and performance of your queries.

For a more detailed understanding of when to use each operation and their respective benefits and drawbacks, read on as we dive deeper into their definitions, syntax, and practical applications.

What is a UNION?

A UNION in SQL combines the results of two or more SELECT statements into a single result set, removing duplicate rows to ensure that each row is unique.

Basic Syntax:

The basic syntax for a UNION operator is as follows:

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

Argument:

The SELECT statements within the UNION must have the same number of columns and similar data types. Additionally, the columns must be in the same order.

For example:

Consider the following tables: employees and managers, each with columns id, first_name, and last_name.

Firstly, the data types for both tables are as follows:

Table: employees
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Michael    | Brown     |
|  2 | Sarah      | Johnson   |
|  3 | Alice      | Smith     |
+----+------------+-----------+

Table: managers
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Robert     | Miller    |
|  2 | Emily      | Davis     |
|  3 | Sarah      | Johnson   |
+----+------------+-----------+

Secondly, the sample data is as follows:

Execute the following SQL query to combine these tables and remove duplicates:

SELECT id, first_name, last_name
FROM employees
UNION
SELECT id, first_name, last_name
FROM managers;

The output is as follows:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Michael    | Brown     |
|  2 | Sarah      | Johnson   |
|  3 | Alice      | Smith     |
|  1 | Robert     | Miller    |
|  2 | Emily      | Davis     |
+----+------------+-----------+

Here, the duplicate row for Sarah Johnson has been removed.

What is a UNION ALL?

A UNION ALL in SQL is similar to a UNION but does not remove duplicate rows. Instead, it combines the results of two or more queries and includes all rows from each query, even if there are duplicate rows.

Basic Syntax:

The basic syntax for a UNION ALL operator is as follows:

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

Argument:

As with the UNION operator, the SELECT statements within the UNION ALL must have the same number of columns in the result sets with similar data types, and the columns must always be in the same order.

For example:

Let’s look at the SQL statement and output for the UNION ALL operator using the same data tables as in the UNION example.

The SQL query is as follows:

SELECT id, first_name, last_name
FROM employees
UNION ALL
SELECT id, first_name, last_name
FROM managers;

When executed, this query returns the following result set:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Michael    | Brown     |
|  2 | Sarah      | Johnson   |
|  3 | Alice      | Smith     |
|  1 | Robert     | Miller    |
|  2 | Emily      | Davis     |
|  3 | Sarah      | Johnson   |
+----+------------+-----------+

All rows, including duplicates, are included in this result set.

What are the Pros and Cons of UNION and UNION ALL?

Understanding the pros and cons of using UNION and UNION ALL helps you choose the appropriate operation based on your specific requirements. Each has its own benefits and drawbacks, particularly regarding performance and handling duplicate data.

PROs of UNION

When it comes to ensuring data integrity and clarity, the UNION operator provides several notable benefits, the most significant being:

Eliminates Duplicates: UNION removes duplicate rows from the result set, ensuring each row is unique. This is useful when you need a distinct set of results from combined datasets.

Cleaner Results: By removing duplicates, UNION provides a cleaner, more concise result set, which can benefit reporting and analysis purposes.

Cons of UNION

However, using UNION can also introduce certain drawbacks, such as:

Performance Overhead: Because UNION must eliminate duplicate rows, additional processing is required. This can slow down the query, especially when dealing with large datasets.

More Resource-Intensive: Sorting and removing duplicates requires more memory and CPU resources, impacting the database system’s performance.

Pros of UNION ALL

UNION ALL offers advantages, particularly in scenarios where performance and data completeness are critical:

Better Performance: UNION ALL does not remove duplicates, which means it can execute faster and use fewer resources than UNION. This makes UNION ALL more efficient for large datasets.

Includes all Records: UNION ALL consists of all rows from the combined queries, which can be useful when you need a complete result set, including duplicates.

Cons of UNION ALL:

Despite its performance benefits, UNION ALL also has several disadvantages, including:

Duplicates Remain: UNION ALL does not remove duplicates, which means the result set may contain repeated rows. This leads to larger result sets and additional handling to remove duplicates.

Potentially Larger Results: Since UNION ALL includes all records, the result set can be significantly larger, which might skew results and not be ideal for specific use cases like reporting or analysis where unique records are required.

Performance Considerations

Performance implications should guide your decision between UNION and UNION ALL. UNION is slower and more resource-intensive due to its deduplication process. In contrast, UNION ALL is faster and uses fewer resources, but it includes all rows, including duplicates.

Your choice depends on your specific use case and whether handling duplicates is more critical than performance.

UNION Examples

Understanding how to use the UNION operation in SQL can significantly improve your ability to manage and manipulate datasets. Here, we will explore a practical example of using UNION to combine data from two different tables and remove duplicates.

Use Case:

In this use case, we want to create a single list that includes both employees and managers, ensuring that each name only appears once, even if it exists in both tables.

Practical Example:

Question: Write a query to combine the lists of employees and managers into a single list, removing any duplicate names:

The data tables used are:

Table: employees
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Michael    | Brown     |
|  2 | Sarah      | Johnson   |
|  3 | Alice      | Smith     |
+----+------------+-----------+

Table: managers 
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | Robert     | Miller    |
|  2 | Emily      | Davis     |
|  3 | Sarah      | Johnson   |
+----+------------+-----------+

The SQL query is as follows:

SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM managers;

The explanation is as follows:

SELECT first_name, last_name FROM employees: This part of the query selects the first_name and last_name columns from the employees table.

UNION: The UNION operator combines the results of the two SELECT statements.

SELECT first_name, last_name FROM managers: This part of the query selects the first_name and last_name columns from the managers table.

When the UNION operation is performed, any duplicate rows (rows with the same first_name and last_name) are removed from the result set.

Lastly, the result is as follows:

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Michael    | Brown     |
| Sarah      | Johnson   |
| Alice      | Smith     |
| Robert     | Miller    |
| Emily      | Davis     |
+------------+-----------+

In the result set, Sarah Johnson appears only once, even though she is present in both the employees and managers tables.

This demonstrates how UNION removes duplicates and combines the results into a single, distinct dataset.

UNION ALL Examples

Our platform, Big Tech Interviews, has several UNION ALL examples. Therefore, let’s look at two of these examples: an intermediate example and an advanced example:

Intermediate Union ALL Example

In this example, we’ll use the UNION ALL operator to combine data from two tables extracted from the Meta SQL Question: Get the percentage of interactions question. This example is particularly useful for understanding how UNION ALL can help when you must include all entries from both tables, including duplicates.

Use Case:

You want to find the number of users with five or more interactions on the last day. You have an interactions table that records interactions between users, and you need to combine the data to get the count of interactions for each user from both user_a and user_b columns.

Practical Example:

Question: Write a query to find the number of users with five or more interactions on the last day.

The table schema is:

Table: interactions
+------------------+-----------+
| Column Name      | Type      |
+------------------+-----------+    
|user_a            |int        |  
|user_b            |int        |
|interaction_date  |date       | 
+------------------+-----------+

The sample data used in this example is:

Table: interactions
+--------+------+----------------+
|user_a  |user_b|interaction_date|
+--------+------+----------------+
|1       |2     |current_date - 1|
|5       |2     |current_date - 1|
|5       |1     |current_date - 1|
|3       |5     |current_date - 1|
|2       |3     |current_date - 1|
|4       |2     |current_date - 1|
|4       |5     |current_date - 1|
|3       |4     |current_date - 1|
|3       |6     |current_date - 1|
|6       |5     |current_date - 1|
|4       |6     |current_date - 1|
|3       |1     |current_date - 1|
+--------+-----------------------+

The SQL query is as follows:

WITH CombinedInteractions AS (
SELECT user_a AS user_id
FROM interactions
WHERE interaction_date = CURRENT_DATE – 1
UNION ALL
SELECT user_b AS user_id
FROM interactions
WHERE interaction_date = CURRENT_DATE – 1
)
SELECT user_id, COUNT(*) AS interaction_count
FROM CombinedInteractions
GROUP BY user_id
HAVING COUNT(*) >= 5

Step-by-Step Solution:

The step-by-step solution includes the following steps:

Create the CTE for Combined Interactions:
1. Using the UNION ALL operator, combine the user_a and user_b columns from the interactions table with a CTE named CombinedInteractions.
2. This ensures that all the interactions from both columns are included, even if they are duplicates.

Main Query:
1. Select user_id and count the number of interactions for each user.
2. Group the results by user_ide and use the HAVING clause to filter users with 5 or more interactions.

SELECT user_id, COUNT(*) AS interaction_count
FROM CombinedInteractions
GROUP BY user_id
HAVING COUNT(*) >= 5

Explanation:

The explanation is as follows:

UNION ALL: Combines all rows from the two SELECT statements without removing duplicates.
CombinedInteractions CTE: Collects all interactions from user_a and user_b columns for the specified date.
Main Query: Aggregates the interactions per user and filters those with 5 or more interactions.

Lastly, the output is as follows:

+---------+-------------------+
| user_id | interaction_count |
+---------+-------------------+
|    3    |         5         |
+---------+-------------------+

This table shows the user with five or more interactions on the previous day, along with their interaction count.

Advanced UNION ALL Example

In this advanced example, we will use the UNION ALL operator to find unique conversation threads in a messaging system. This example (taken from Big Tech Interview’s LinkedIn SQL Question: Conversation threads) will illustrate how UNION ALL can help combine data and apply further transformations to achieve the desired results.

Use Case:

You need to find the number of unique conversation threads in a messaging system. Each conversation thread is defined by a pair of sender and receiver, but note that you can swap the sender and receiver, and the thread still represents the same conversation.

Practical Example:

Question: Write a query to find how many unique conversation threads there are.

The database table schema is as follows:

Table: messenger_sends
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
|sender_id         |int      |  
|receiver_id       |int      |    
+------------------+---------+

The sample data is as follows:

Table: messenger_sends
+-----------+-----------+
|sender_id  |receiver_id|
+-----------+-----------+
|1          |2          |
|1          |2          |
|2          |1          |
|4          |2          |
|3          |2          |
|2          |3          |
|1          |3          |
|1          |4          |
|1          |4          |
|4          |3          |
|4          |3          |
+-----------+-----------+

The SQL query is as follows:

WITH AllThreads AS (
SELECT sender_id, receiver_id
FROM messenger_sends
UNION ALL
SELECT receiver_id AS sender_id, sender_id AS receiver_id
FROM messenger_sends
),
UniqueThreads AS (
SELECT DISTINCT
LEAST(sender_id, receiver_id) AS user1,
GREATEST(sender_id, receiver_id) AS user2
FROM AllThreads
)
SELECT COUNT(*) AS unique_threads
FROM UniqueThreads;

Step-by-Step Solution:

The steps in this solution include:

Create the CTE for ALL Threads:
1. Use a CTE named AllThreads to combine the sender_id and receiver_id columns from the messenger_sends table and its inverse.
2. This ensures all possible pairs (both directions) are included.

WITH AllThreads AS (
SELECT sender_id, receiver_id
FROM messenger_sends
UNION ALL
SELECT receiver_id AS sender_id, sender_id AS receiver_id
FROM messenger_sends
)

Create the CTE for Unique Threads:
1. Use another CTE called UniqueThreads to find distinct conversation threads.
2. Use the LEAST and GREATEST functions to standardize the order of sender and receiver, ensuring that (1,,2) and (2,1) are treated as the same thread.

UniqueThreads AS (
SELECT DISTINCT
LEAST(sender_id, receiver_id) AS user1,
GREATEST(sender_id, receiver_id) AS user2
FROM AllThreads
)

Main Query: Select the count of unique threads from the UniqueThreads CTE.

SELECT COUNT(*) AS unique_threads
FROM UniqueThreads;

Explanation:

UNION ALL: Combines all rows from both SELECT statements, including duplicates.

AllThreads CTE: Collects all conversation threads, ensuring both (sender, receiver) and (receiver, sender) pairs are included.

UniqueThreads CTE: This function ensures that each thread is counted only once by using the LEAST and GREATEST functions to standardize the order of the user pairs.

Main Query: Counts the number of unique conversation threads.

Output:

The result_set returns the total number of unique conversation threads in the messenger_sends table.

+----------------+
| unique_threads |
+----------------+
|       5        |
+----------------+

This example demonstrates how advanced SQL CTEs with UNION ALL can be used to perform complex data transformations and ensure accurate results.

SQL UNION & UNION ALL Best Practices

In order to ensure optimal performance and maintainability of your SQL queries using UNION and UNION ALL, it is vital to adhere to some best practices:

Use UNION ALL When Possible: Prioritize using UNION ALL over UNION unless you need to eliminate duplicate rows. UNION ALL is generally faster because it doesn’t require the overhead of checking the duplicates across result sets.

Column Order and Data Types: Ensure the number of columns and data types match across all SELECT statements combined. The column names in the result set are taken from the first SELECT statement, so using consistent and meaningful aliases in the first query is essential.

Order of Execution: Be mindful that the ORDER BY clause is applied after the UNION (UNION ALL) operation is complete. When sorting results, use the column names or aliases from the first SELECT statement, ensuring clarity and avoiding potential errors due to misaligned column references.

Minimize Columns: Only include necessary columns in your SELECT statements. Reducing the number of columns can improve query performance and make the result set easier to manage.

Filtering Data: Apply WHERE clauses before the UNION (UNION ALL) operation to filter data as early as possible. This reduces the volume of data needing to be processed, resulting in more efficient query execution.

Use Consistent Formatting: Maintain consistent formatting and indentation across all SELECT statements within your UNION or UNION ALL operation. This improves readability, making it easier to identify and debug issues.

Test and Optimize: Test your queries with different datasets to understand the performance implications. Use EXPLAIN plans to analyze and optimize the execution path of your queries, helping identify bottlenecks and optimizing index usage.

Document Your Queries: Include comments in your SQL code to explain the purpose of each SELECT statement and the UNION/UNION ALL operation. This is helpful for complex queries and ensures that others can understand and maintain the code efficiently.

By following these best practices, you can make sure that your SQL queries using UNION and UNION ALL are efficient, maintainable, and easy to understand.

These guidelines help manage performance and readability, which is critical when dealing with large datasets or complex query requirements.

Additional Resources

To further improve your understanding of SQL UNION and UNION ALL and to broaden your SQL knowledge, consider exploring the following resources from Big Tech Interviews. These articles include comprehensive guides, practical examples, and insights that complement the concepts covered in this guide.

Amazon SQL Interview Questions: Dive into a collection of SQL interview questions specifically curated for Amazon’s technical interviews. Get practical examples and detailed solutions to help you prepare effectively for SQL interviews at Amazon.

SQL Joins—A Complete Guide: Understanding joins is essential for working with complex SQL queries, including those involving UNION and UNION ALL operations. This detailed guide will help you master the various types of SQL joins.

SQL CASE WHEN: Learn how to use the SQL CASE WHEN statement with practical examples and best practices. This resource is handy for implementing conditional logic in your SQL queries, which can be combined with UNION and UNION ALL for more complex data manipulations.

Meta Data Engineer Interview: A Complete Guide: This comprehensive guide covers the interview process for Data Engineer roles at Meta, including SQL-related questions and scenarios.

SQL Cheat Sheet: The SQL Cheat Sheet is a handy reference guide covering essential SQL syntax, functions, and commands. It is an excellent resource for quick reference and helps reinforce your SQL knowledge.

By exploring these resources, you can further solidify your understanding of SQL UNION and UNION ALL, improve your query-writing skills, and prepare effectively for technical interviews. These articles provide additional insights and examples that will help you master SQL and apply it confidently in both interviews and real-world scenarios.

Conclusion

Mastering the use of UNION and UNION ALL in SQL is critical for anyone aiming to improve their data management capabilities. These powerful SQL constructs enable you to combine datasets in versatile ways, either by removing duplicates with UNION or retaining all records with UNION ALL. You can write more efficient and effective SQL queries by understanding their syntax, use cases, and performance implications.

Whether you are preparing for an SQL interview or looking to improve your skills for professional growth, the knowledge you’ve gained from this guide will be invaluable. Applying the best practices discussed, you can confidently optimize your SQL queries and handle complex data manipulation tasks.

For further practice and deeper insights into SQL and technical interview preparation, visit Big Tech Interviews. Here, you’ll find a wealth of resources, including comprehensive guides, practical examples, and up-to-date interview questions designed to give you a competitive edge.

Frequently Asked Questions (FAQs)

Here is a list of FAQs:

Should I use UNION or UNION ALL?

The choice between UNION and UNION ALL depends on your specific requirements:

Use UNION if you must eliminate duplicate rows in the combined result set. This ensures that each row appears only once.

Use UNION ALL if you want to retain all rows, including duplicates. This can be more performant as it avoids the overhead of removing duplicates.

What is the difference between UNION ALL and UNION DISTINCT?

There is no term “UNION DISTINCT” in SQL. The term “UNION” implicitly includes the DISTINCT operation to remove the duplicate rows from the result set. SQL automatically eliminates duplicates when using UNION, so there is no need to specify “DISTINCT.”

In SQL, the terms are:

UNION: Combines the results of two or more SELECT statements and removes duplicate rows.
UNION ALL: Combines the results of two or more SELECT statements and retains all rows, including duplicates.

What is the difference between UNION and JOIN?

UNION combines the results of two or more SELECT statements into a single result set by stacking the rows on top of each other.

On the other hand, JOIN combines rows from two or more tables based on a related column between them, creating a single row for each match found.

In summary, understanding the differences between these operations is vital, especially when deciding between using a full join vs. UNION ALL for combining datasets in SQL queries.

Does UNION remove duplicates?

Yes, the UNION operation removes duplicate rows from the combined result set, ensuring each row appears only once. If you want to retain duplicates, use UNION ALL instead.

Do you want to ace your SQL interview?

Practice free and paid real SQL interview questions with step-by-step answers.

UNION vs UNION ALL in SQL: A Comprehensive Guide

Tim S

What is the Difference Between UNION and UNION ALL?

What is a UNION?

What is a UNION ALL?

What are the Pros and Cons of UNION and UNION ALL?

PROs of UNION

Cons of UNION

Pros of UNION ALL

Cons of UNION ALL:

Performance Considerations

UNION Examples

UNION ALL Examples

Intermediate Union ALL Example

Advanced UNION ALL Example

SQL UNION & UNION ALL Best Practices

Additional Resources

Conclusion

Frequently Asked Questions (FAQs)

Do you want to ace your SQL interview?

Do you want to Ace your SQL interview?