UNION And UNION ALL In SQL

UNION in SQL

The UNION operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set. It has a default feature to remove duplicate rows from the tables.

The syntax for using the UNION operator is as follows,

SELECT product, price
FROM orders
UNION
SELECT product, price
FROM sales;

UNION ALL  in SQL

The UNION ALL operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set, including all rows, even if there are duplicates. The SELECT statements must return the same number of columns and data types, but the UNION ALL operator does not eliminate duplicates as the UNION operator does.

The syntax for using the UNION ALL operator is as follows,

SELECT product, price
FROM orders
UNION ALL
SELECT product, price
FROM sales;

Some basic rules for using the UNION & Union ALL operator in SQL,

1. Column count and data types must match: The SELECT statements used in the UNION operator must return the same number of columns and the data types of the columns must match.
2. Order of columns must match: The order of columns in the SELECT statements must be the same.

When comparing UNION vs. UNION ALL, there is one major difference

• UNION only returns unique
• UNION ALL returns all records, including duplicates.

Difference between Union and Union All 

UNION	UNION ALL
It combines the result set from multiple tables and returns distinct records into a single result set.	It combines the result set from multiple tables and returns all records into a single result set.
It has a default feature to eliminate duplicate rows from the output.	It has no feature to eliminate duplicate rows from the output.
Its performance is slow because it takes time to find and then remove duplicate records.	Its performance is fast because it does not eliminate duplicate rows.
Most database users prefer to use this operator.	Most database users do not prefer to use this operator.
Syntax of UNION operator: SELECT column_list FROM table1 UNION SELECT column_list FROM table2;	Syntax of UNION ALL operator: SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2;

Summary

"UNION" and "UNION ALL" are SQL keywords used to combine the results of two or more SELECT statements into a single result set. The main difference between the two is that "UNION" eliminates duplicate rows from the combined result set, while "UNION ALL" retains all rows, including duplicates. Both keywords are used to join results from multiple SELECT statements and provide a single, consolidated view of the data.