union:-
- union command remove the duplicates and selects only distinct values by default
- UNION command must have the same number of columns and columns must also have similar data types.
- union command more expensive than union all
Union all:-
- Union all command Combines all the data
- union all command does not remove duplicates and pull out all rows from all tables and combines them into a table.
Example – I have created two tables and inserted some records in both tables:-
- when we run the UNION & UNION ALL then we get below output
- when we will compare the execution plan of UNION and UNION ALL then we will find that UNION is more expensive than UNION ALL.