What is the difference between UNION and UNION ALL?
Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.
-UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
-UNION ALL does not remove duplicates, and it therefore faster than UNION.
Note: While using this commands all selected columns need to be of the same data type. Example: If we have two tables, 1) Employee and 2) Customer
1) Employee table data:
2) Customer table data:
3) UNION Example (It removes all duplicate records):
4) UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):
-UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
-UNION ALL does not remove duplicates, and it therefore faster than UNION.
Note: While using this commands all selected columns need to be of the same data type. Example: If we have two tables, 1) Employee and 2) Customer
1) Employee table data:
2) Customer table data:
3) UNION Example (It removes all duplicate records):
4) UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):
No comments:
Post a Comment