What does union and union all do in SQL?

Data Science, AI and ML
,
#1

Sql, Union

#2
  • Union: It combines the result set from multiple tables with eliminating the duplicate records.It performs a distinct on the result set.
  • Union all: It combines the result set from multiple tables without eliminating the duplicate records. it does not perform distinctly on the result set.
1 Like
#3

UNION - Removes duplicates and return distinct set of rows. Combines results from both queries/tables and apply “distinct” on result set. “Distinct” is additional and costly operation, so it takes more time giving less performance.

UNION ALL - Brings all rows from both queries, along with duplicate records. Combines results from both queries/tables and returns result set. So it takes less time so high in performance.

Table A - 120 rows with 10 duplicates from Table B

Table B - 30 rows with 10 duplicates from Table A

*Here table A/B can be a query too.

Table A UNION Table B - 140 Rows

Table A UNION ALL Table B - 150 Rows