What is UNION ALL operator in sqlite?

The UNION ALL operator is an operator in SQL used to merge the result sets of two or more SELECT statements. This means it combines all result rows from different SELECT statements into a single result set, including duplicate rows.

An important use case for UNION ALL is when you need to collect similar data from multiple tables, but these tables have matching structures (i.e., the same number and type of columns), and you do not care about duplicate rows in the result.

For example, suppose there are two sales data tables: sales2019 and sales2020. Each table has two columns: product_id and amount_sold. Now we want to find an overview of all sales records from both years.

SQL statement:

sql
SELECT product_id, amount_sold
FROM sales2019
UNION ALL
SELECT product_id, amount_sold
FROM sales2020;

This query returns all sales records from sales2019 and sales2020, including all duplicate data rows. If the same product is sold in both tables, these records will appear in the final result set. This is very useful for performing comprehensive data analysis and generating complete sales reports.