Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: UNION deduplicates; UNION ALL does not—semantic and performance differ. **UNION**: Combines; removes duplicates (implicit DISTINCT); slower (sort). **UNION ALL**: Combines; keeps all rows; faster. **Use UNION**: When dedup required. **Use UNION ALL**:...
Architectural Logic: UNION deduplicates; UNION ALL does not—semantic and performance differ. UNION: Combines; removes duplicates (implicit DISTINCT); slower (sort). UNION ALL: Combines; keeps all rows; faster. Use UNION: When dedup required. Use UNION ALL: When impossible or acceptable. Example: Disjoint date ranges = UNION ALL correct and faster. Scalability: UNION ALL avoids shuffle for dedup; UNION is O(n log n). Cost: UNION adds compute; know source data. Best Practice: Prefer UNION ALL; apply DISTINCT separately if needed; validate semantics.
Red Flag: UNION when source tables are disjoint—wasted sort. Pro-Move: UNION ALL for ETL appends; verify no duplicates in source; cite performance difference in distributed systems.
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.