Revature Interview Practice Test

What is the key difference between UNION and UNION ALL in SQL?

UNION returns all rows including duplicates

UNION ALL removes duplicate rows

UNION combines tables without filtering

UNION removes duplicate rows, while UNION ALL does not

The distinction lies in how each command handles duplicate rows in the result sets it generates. When using UNION, the command combines the results of two or more SELECT queries and automatically removes any duplicate rows from the final result set. This means that if the same row appears in both result sets, it will only be included once in the output.

On the other hand, UNION ALL retains all rows from the combined SELECT statements, including duplicates. Therefore, if identical rows are present in both result sets, they will appear as many times as they occur in the source data.

This fundamental difference is pivotal when deciding which command to use based on the requirement for unique versus inclusive data. Choosing UNION is appropriate when uniqueness is essential, while UNION ALL is preferred for complete data inclusion, including duplicates.