SQL UNION and UNION ALL

Understanding SQL UNION and UNION ALL

SQL UNION and UNION ALL are set operations that combine the results of two or more SELECT statements into a single result set. While they serve similar purposes, they handle duplicate rows differently and have different performance characteristics.

Key Differences

UNION removes duplicate rows from the final result
UNION ALL keeps all rows, including duplicates
UNION requires additional processing time to remove duplicates
UNION ALL is generally faster as it skips duplicate checking

Basic UNION Operation

Simple UNION Example

Combine results from multiple queries while removing duplicates.

This query returns a unique list of users who have either uploaded videos or made comments. If a user has done both, they appear only once in the results.

UNION with Different Tables

Combine related data from different tables, ensuring compatible column types.

UNION ALL Operation

When to Use UNION ALL

Use UNION ALL when you need all rows and know duplicates are either impossible or desired.

Performance Considerations

When choosing between UNION and UNION ALL, consider:

UNION ALL: Faster performance as it skips duplicate removal
UNION: Additional overhead for duplicate checking
Use UNION ALL when you:
- Know duplicates are impossible
- Need to preserve duplicate rows
- Want better performance

Common Pitfalls and Best Practices

Column Compatibility

Ensure columns in UNIONed queries are compatible in number and data type.

NULL Handling

UNION treats NULL values as equal when removing duplicates.

Ordering Results

Use ORDER BY at the end of the entire UNION query, not within individual SELECTs.

Summary

Use UNION when you need to combine results and remove duplicates
Use UNION ALL when you need all rows or better performance
Ensure the same number of columns and compatible data types
Apply ORDER BY only to the final result set
Consider performance implications when choosing between UNION and UNION ALL

Interview Master

SQL Learning Hub