UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table. , while UNION ALL selects all records from both the tables.
The main difference between “UNION” and “UNION ALL” in database management systems (DBMS) lies in how they handle duplicate rows.
- UNION: This operator combines the result sets of two or more SELECT statements, removing any duplicate rows from the final result set. It effectively performs a set union operation, meaning it only includes distinct rows in the result.
- UNION ALL: This operator also combines the result sets of multiple SELECT statements, but it retains all rows from the combined result sets, including duplicates. It doesn’t perform any elimination of duplicate rows.
So, in summary, if you want to eliminate duplicate rows from the combined result set, you would use “UNION”. If you want to keep all rows, including duplicates, you would use “UNION ALL”.