Difference between UNION and UNION ALL

What is UNION?

The UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. It removes duplicates from the combined result set.

Syntax

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

What is UNION ALL?

The UNION ALL is used to combine the result sets of two or more SELECT queries into a single result set. It's used to retrieve data from multiple tables or queries and present them as a unified result. The key characteristic of UNION ALL is that it includes all rows from all the involved queries, including duplicates.

Syntax

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

UNION Vs UNION ALL: Example

UNION 

We have two tables named ‘Table 1’ and ‘Table 2’ with employee data as specified below:
Creating Table 1

Code:

CREATE TABLE table1 (

empId INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);

Inserting Values into Table 1:

INSERT INTO table1 VALUES (1, 'John', 'Sales');

INSERT INTO table1 VALUES (2, 'Mary', 'Marketing');

INSERT INTO table1 VALUES (3, 'Peter', 'Finance');

INSERT INTO table1 VALUES (4, 'Tom', 'Marketing');

INSERT INTO table1 VALUES (5, 'Lisa', 'Sales');

INSERT INTO table1 VALUES (6, 'Jane', 'HR');

Creating Table 2

Code:

CREATE TABLE table2 (

empId INTEGER PRIMARY KEY,

name TEXT NOT NULL,

dept TEXT NOT NULL

);

Inserting Values into Table 2:

INSERT INTO table2 VALUES (7, 'John', 'Sales');

INSERT INTO table2 VALUES (8, 'Tom', 'Marketing');

INSERT INTO table2 VALUES (9, 'Lisa', 'Sales');

INSERT INTO table2 VALUES (10, 'Jane', 'HR');

INSERT INTO table2 VALUES (11, 'Mary', 'Marketing'); -- duplicate row

Applying UNION to the data of Table 1 and Table 2:

SELECT name, dept FROM table1

UNION

SELECT name, dept FROM table2;

Output

Explanation

In the example above, we choose information from tables table1 and 2, which are two separate tables. From both tables, we select the columns "empID", "name", and "dept”. Then using UNION, we combine the data. All duplicate rows will be eliminated, leaving the final result set with only distinct rows.

Read about similar article - Unions Clause.

UNION ALL

Refer to Tables 1 and 2 from the above example of UNION. Here, we will apply UNION ALL to the data of Table 1 and Table 2:

SELECT name, dept FROM table1

UNION ALL

SELECT name, dept FROM table2;

Output

Explanation

In the illustration mentioned above, Table 1 and Table 2's results are combined using UNION ALL. We choose the columns  "empID", "name", and "dept”  from both tables, then using UNION ALL; we combine the information. Contrary to UNION, duplicate rows won't be eliminated from the result set.

When Should You Use UNION and UNION ALL?

It's crucial to consider your SQL query's specific requirements when selecting whether to use UNION or UNION ALL. 

1. The UNION operator should be used to integrate datasets and eliminate any duplicate rows. This is helpful when working with data that might have values that are similar or when you wish to combine data from various sources into a single, cohesive dataset.
    
2. On the other hand, UNION ALL is the operator to employ if you need to join datasets while keeping all rows, including duplicates. For merging datasets with overlapping values or when you want to keep all the rows from each dataset but combine them into a single result set.
    
3. As the database must carry out additional operations to delete duplicate rows, UNION can be computationally more expensive than UNION ALL. Hence, UNION ALL can be used to provide faster query results if you don't need deduplication. 
    
4. The decision between UNION and UNION ALL ultimately comes down to the particular requirements of your query and the peculiarities of your data.

Frequently Asked Questions

Which is faster, UNION or UNION ALL?

The UNION ALL operation is generally faster than the UNION operation. UNION combines and removes duplicates from the result set, which involves additional processing. UNION ALL, on the other hand, simply combines the result sets without removing duplicates, resulting in a faster operation.

Does UNION ALL remove duplicates?

No, the UNION ALL operation does not remove duplicates. It combines the result sets from multiple queries without any duplicate elimination. If you want to combine result sets while removing duplicates, you should use the UNION operation.

What are the two main differences between structure and union?

The two main differences between structures and unions are memory allocation and member access. In a structure, each member has its own memory space, leading to a larger overall memory consumption. However, a union shares memory space among its members, so it only requires as much memory as its largest member. Each member in a structure can be accessed independently using its name. In a union, only one member can hold a value at a time, and accessing one member might overwrite the value of another.

Do UNION and UNION ALL operate differently with NULL values?

Yes. UNION treats NULL values as equal, so it will remove duplicate rows even if they differ only in the value of a NULL column. UNION ALL, however, includes all rows, including duplicates, regardless of whether they contain NULL values.

Conclusion

The two fundamental SQL operators, UNION and UNION ALL, enable you to combine the results of two or more SELECT statements. You can choose the best operator for your particular use case by being aware of the distinctions between these two operators. While UNION eliminates duplicate rows, UNION ALL contains every row, including duplicates.

So it's time for you now to refer to other articles based on a similar topic, i.e., UNION and UNION ALL clauses:

• Difference Between Union and Join
• Difference between structure and union in C
• SQL Introduction
• Difference Between List and Set
• Difference between sql and nosql
   

We hope this blog has helped you enhance your knowledge of the difference between UNION and UNION ALL. Do visit here to study database management systems in depth and clarify all your concepts. 

Happy learning!