SQL UNION Vs UNION ALL Operator
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
|B-1||My SQL Complete Reference||A-2|
|B-2||Art Of SQL||A-2|
|B-4||Programming with T-SQL for Beginners||A-2|
In below query returns all same rows from pair of above two tables,
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
|It returns distinct records by combining the result set from various tables.||It mixes the results from several tables without removing duplicate records.|
|UNION is a SQL operator that joins the results of two separate SQL queries. The tables can be the same or different, but the data must be the same. If product id is utilised in two tables, such as Product and Order, then UNION can be used to combine two SQL queries that pull product id from these two tables.||UNION ALL is a SQL operator that joins the results of two independent SQL queries. The tables can be the same or different, but the data must be the same. If product id is utilised in two tables, such as Product and Order, then UNION ALL can be used to combine two SQL queries that pull product id from these two tables.|
|On the result set, it does a distinct.||On the result set, it does not execute distinct.|
|The Union operator must be specified.||The Union All Operator must be specified.|
|It contains a built-in functionality that removes duplicate rows from the output.||It does not have a feature to remove duplicate rows from the output.|
|Its performance is slow since finding and removing duplicate records takes time.||Its performance is quick because duplicate rows aren't removed.|
|UNION appears to be slower than UNION ALL in practise because it produces a large amount of data with duplicates, which takes longer to travel from the database server to the client machine.||In practise, UNION ALL looks to be slower than UNION because it returns a large amount of data with duplicates, which takes longer to travel from the database server to the client workstation.|
|Running distinct on the output of UNION ALL is the same as running UNION. If both SQL queries return product id 10, for example, it will only display once if you use UNION.||If you use UNION ALL, it will appear twice.|
|The target lists of all queries combined with a UNION, intersect, or except operator must contain an equal number of expressions. For example, if query 1's result has three columns and query 2's result has two columns, you can't use the UNION command to join them.||It will not be necessary for them to have an equal number of experiences in their target list.|