UNION vs UNION ALL in SQL

In the SQL universe, the ability to combine data from multiple queries is a fundamental requirement, and SQL offers powerful functions like UNION and UNION ALL to achieve this. 

As you will see, understanding the subtle differences between UNION and UNION ALL functions in SQL is imperative for efficient data querying and management. This tutorial explains their key similarities, differences, and usage scenarios using companion datasets to help you optimize your SQL queries.

The Short Answer: SQL UNION vs. UNION ALL 

The key difference is that UNION removes duplicate records, whereas UNION ALL includes all duplicates. This distinction not only changes the number of rows in the query result, but it also impacts performance.

Let's consider two sample tables, employees_2023 and employees_2024, representing employee records for two different years.

SELECT *
FROM employees_2023

SELECT *
FROM employees_2024

Now, let’s join our two tables using the UNION function.

SELECT employee_id, name, department FROM employees_2023 
UNION 
SELECT employee_id, name, department FROM employees_2024;

In this result, duplicate records are removed. Now, let’s compare this with the result we obtain after using UNION ALL.

SELECT employee_id, name, department FROM employees_2023 
UNION ALL 
SELECT employee_id, name, department FROM employees_2024;

In this result, all records are included, and duplicates are not removed.

What is UNION in SQL?

The UNION function combines the results of two or more SELECT queries into a single result set, removing duplicate rows. Each SELECT statement within the UNION must have the same number of columns. Also, they have to have similar data types, and the columns must also be in the same order. To understand the function, let’s create two sample tables below.

CREATE TABLE sales_team (
    employee_id INT,
    employee_name VARCHAR(50)
);
INSERT INTO sales_team (employee_id, employee_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
CREATE TABLE support_team ( 
employee_id INT, 
employee_name VARCHAR(50)
 );
INSERT INTO support_team (employee_id, employee_name) VALUES 
(3, 'Charlie'), 
(4, 'David'), 
(5, 'Eve');

The above code will create the two tables we’ll use in this tutorial, sales_team and support_team, respectively. You can view the tables using the code below.

SELECT * 
FROM sales_team

Records in the sales_team table. Image by Author 

SELECT * 
FROM support_team

Records in the support_team table. Image by Author 

These are small tables with three records each, and the column names are self-explanatory. Now that we have our two tables created, let’s apply the UNION function.

SELECT employee_id, employee_name
    FROM sales_team
   UNION
SELECT employee_id, employee_name
    FROM support_team;

Output from the UNION function. Image by Author 

You can see that the duplicate entry of employee_id, which takes the value of 3, is removed. 

What Are the Use Cases for UNION in SQL?

Here are some common use cases for the UNION function.

• Combining Results From Different Tables: When you want to combine data from multiple tables and ensure no duplicate records, UNION is the go-to function.

• Handling Different Data Sources: UNION is useful when combining tables from different data sources. 

• Removing Duplicates Across Queries: When you want to ensure the uniqueness of the combined result set, use UNION.

What is UNION ALL in SQL?

The UNION ALL function combines the results of two or more SELECT queries, including all duplicate rows. This function is faster than UNION because it doesn’t bother removing duplicates.

SELECT employee_id, employee_name
    FROM sales_team
UNION ALL
SELECT employee_id, employee_name
    FROM support_team;

Output from the UNION ALL function. Image by Author 

You can see that, in this case, the duplicate entries are repeated and not removed.

What Are the Use Cases for UNION ALL in SQL?

Here are some common use cases for the UNION ALL function.

• Combining Results With Duplicates: Use UNION ALL when you need to combine results from multiple queries and preserve all duplicate rows.
• Performance Considerations: UNION ALL is more time-efficient than the UNION function, because it doesn't require the additional step of removing duplicates.
• Aggregating Data From Different Periods: When aggregating data from different periods or sources, and you need to preserve the duplicate entries, UNION ALL is preferred.
• Reporting and Analysis: For reporting purposes where every record, including duplicates, is necessary, UNION ALL is suitable.

UNION and UNION ALL: Similarities and Differences

Let's create a summary table of the similarities and differences. 

UNION and UNION ALL Syntax Differences Across SQL Dialects 

While the basic syntax remains the same across different SQL dialects, there are slight variations depending on the platform. If you look at the code then in most of the platforms, such as SQL Server, Oracle, MySQL, PostgreSQL, or BigQuery, the code will look something similar to what is shown below. 

SELECT employee_id, employee_name
    FROM sales_team
UNION ALL
SELECT employee_id, employee_name
    FROM support_team;

Basic commands in these SQL dialects remain the same, with some nuances and differences:

1. Handling NULLs and Collations: Different SQL dialects may have different rules for handling NULL values and string collation.
2. Performance Optimizations: The performance optimizations and execution efficiency might vary.
3. Error Handling and Warnings: Different dialects may handle errors and warnings differently when using these functions.
4. Platform-Specific Functions and Features: Some SQL dialects might offer additional functions or features that can be used in conjunction with UNION and UNION ALL for more complex scenarios.

The command is significantly different in the case of PySpark, which operates in a distributed environment. The code is given below, assuming df1 and df2 are the names of the two data frames consisting of the two tables we created above. :

df1.union(df2)

3 Rules and Common Errors When Using SQL UNION

While UNION and UNION ALL are powerful, they are strict regarding syntax. If your query is returning an error, it is likely violating one of these three golden rules.

1. The Column Count Mismatch

The most common error users encounter is a mismatch in the number of columns. To combine two datasets, the "shape" of the tables must be identical. You cannot stack a table with 3 columns on top of a table with 2 columns.

The error:

-- This will fail because the column counts do not match
SELECT employee_id, name, department 
FROM employees_2023
UNION
SELECT employee_id, name 
FROM employees_2024;提供：

The fix: Ensure both SELECT statements request the exact same number of columns. If the second table is missing data (like department), you can select NULL as a placeholder to make the counts match:

SELECT employee_id, name, department FROM employees_2023
UNION
SELECT employee_id, name, NULL as department FROM employees_2024;提供：

2. Data Type Compatibility

SQL combines results based on position, not column name. The data type of the first column in your first query must match (or be compatible with) the data type of the first column in your second query.

The error: If you accidentally swap the order of columns, SQL tries to stack a Number (ID) on top of a String (Name), causing a data type conversion error.

-- This fails because Column 1 (ID - INT) cannot combine with Column 1 (Name - VARCHAR)
SELECT employee_id, name FROM employees_2023
UNION
SELECT name, employee_id FROM employees_2024;提供：

The Fix: Always verify that your columns are listed in the exact same order in every SELECT statement within the UNION.

3. ORDER BY Limitations

A frequent point of confusion is where to place the ORDER BY clause. You cannot sort the individual sub-queries inside a UNION operation; you can only sort the final combined result.

The error: Using ORDER BY before the final query will throw a syntax error.

-- INCORRECT SYNTAX
SELECT employee_id, name FROM employees_2023 
ORDER BY employee_id -- You cannot sort here!
UNION
SELECT employee_id, name FROM employees_2024;提供：

The fix: Place the ORDER BY clause at the very end of your query. This applies the sort to the entire combined result set.

-- CORRECT SYNTAX
SELECT employee_id, name FROM employees_2023
UNION
SELECT employee_id, name FROM employees_2024
ORDER BY employee_id; -- Sorts the final result提供：

Final Thoughts

It is essential to understand the application of UNION as opposed to UNION ALL in managing data efficiently using SQL. Where UNION selects only distinct records, UNION ALL selects all of them,  affecting performance and result set size. Use this knowledge to choose the appropriate function for your specific requirements. 

For further learning, consider exploring the following sources:

• Introduction to SQL: Learn how to create and query relational databases using SQL.
• SQL Fundamentals: Gain the fundamental SQL skills you need to interact with and query your data. 
• SQL Associate Certification: Demonstrate your SQL ability by extracting appropriate data from a database, and using it to answer common data questions.
• Intermediate SQL: With this hands-on course, you’ll learn everything you need to know to analyze data using your own SQL code today.