Explain the difference between UNION and UNION ALL.

UNION combines the result sets of two or more SELECT statements and automatically removes duplicate rows, effectively performing an implicit DISTINCT operation. In contrast, UNION ALL combines result sets without checking for or removing duplicate rows, preserving all rows from the combined inputs.

Mechanics and Performance

The core difference lies in the deduplication step. UNION requires the database engine to perform additional work to identify and eliminate duplicates. This typically involves sorting the combined dataset (O(N log N) complexity) or building a hash table (average O(N) complexity), which consumes significant CPU, memory, and potentially I/O resources, especially for large datasets. UNION ALL, on the other hand, is a simpler concatenation operation (O(N) complexity) that merely appends the rows from one result set to another.

Both operators require that the SELECT statements have the same number of columns, and corresponding columns must have compatible data types.

Trade-offs and Scalability

Due to the deduplication overhead, UNION is generally much slower and more resource-intensive than UNION ALL. In distributed systems like Apache Spark, the UNION operation can trigger a "shuffle" across network partitions to gather and sort data for deduplication, leading to substantial network I/O and latency. For very large datasets, UNION can lead to disk spills if memory is insufficient, further degrading performance.

UNION ALL is the preferred choice for performance and scalability unless the business logic explicitly requires unique rows. It's often used when combining data from different sources that are known to have disjoint sets of primary keys or when duplicates are acceptable/expected.

-- Example: Combining two tables
SELECT id, name FROM employees_current
UNION ALL -- Faster, keeps all rows including duplicates
SELECT id, name FROM employees_archive;
-- If uniqueness is critical:
SELECT id, name FROM employees_current
UNION -- Slower, removes duplicate (id, name) pairs
SELECT id, name FROM employees_archive;

In the interview, also mention that UNION's deduplication can be expensive in a distributed environment, often necessitating a data shuffle, which is a major performance consideration for large-scale data processing.