sql / intermediate
Snippet
Combining Query Sets with UNION ALL to Avoid Duplicate Elimination Overhead
The UNION ALL operator merges the result sets of two structurally compatible queries while retaining all rows. Unlike UNION, which performs sorting and distinct filtering to eliminate duplicate rows, UNION ALL avoids memory and CPU overhead when uniqueness is already guaranteed or duplicates are acceptable.
snippet.sql
sql
1
2
3
4
5
SELECT customer_id, city, 'Active' AS statusFROM active_customersUNION ALLSELECT customer_id, city, 'Archived' AS statusFROM archived_customers;
Breakdown
1
SELECT customer_id, city, 'Active' AS status
Retrieves customer rows from the first query and appends a static status label.
2
FROM active_customers
Queries the table containing currently active customers.
3
UNION ALL
Combines rows from both result sets without executing expensive duplicate elimination.
4
SELECT customer_id, city, 'Archived' AS status
Retrieves matching structural columns from the secondary table.
5
FROM archived_customers;
Queries the table containing archived historical records.