SQL UNION Vs UNION ALL Operator

Though both UNION and UNION ALL are used to merge the results of two SELECT queries, the main distinction is that UNION excludes duplicate records while UNION ALL does.

The distinction between UNION and UNION ALL can be a difficult SQL query to answer, particularly for those who have never used this important keyword before.

All major databases, including Oracle, Microsoft SQL Server, MySQL, and PostgreSQL, fully support the 2 keyword.

SQL UNION Operator

The term "UNION" refers to the merging of two or more data sets into a single set.

When employing select statements in SQL Server, UNION is used to merge two queries into a single result set.

The union function extracts all of the rows described in the query. Before being utilised in a query, Union holds a few conditions. The rows to be retrieved must all come from the same columns in the tables, for example.

UNION is extremely handy in a variety of situations, such as when you need to generate a list from multiple tables that all contain data from the same set.

Example: Assume we have two tables, "Student" and "Student2," each containing the following information:

Using the UNION query, the following SQL expression returns the distinct names of cities from both tables:

SELECT City FROM student  
UNION  
SELECT City FROM student2  
ORDER BY City;

Because the UNION operator only returns distinct values, we will get the following output after executing the above line.

SQL UNION ALL Operator

The UNION ALL function is used to retrieve all rows from a pair of tables.

UNION ALL is subject to the same conditions. The sole difference between UNION and UNION ALL is that Union extracts only the rows provided in the query, whereas UNION ALL extracts all rows from both queries, including duplicates (repeated values).

Example: Let's look at an example of the differences between UNION and UNlON ALL operators. Assume we have two tables, "Author" and "Book," each containing the following information:

Using the UNION ALL query, the following SQL statement returns all AID values from both tables, including duplicates:

Lets look at Book table

In below query returns all same rows from pair of above two tables,

Mysql,Sqlite,Ms access

SELECT AID FROM Author
UNION ALL  
SELECT AID FROM Book
ORDER BY AID ;

Because the Union ALL operator provides full records without removing different values.

Output: we will get the following output after executing the preceding code.

SQL Difference Between UNION And UNION ALL Operator

Here, We discussed the contrasts in union vs union all operator