|previous : next|
SQL: Unnecessary UNION instead of UNION ALL
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The UNION operator removes duplicate rows, whereas
UNION ALL retains them. It isn't always possible for an optimiser to prove that there are no duplicates possible. If you, as a developer, know that there can't be any duplicates, or if you don't care about the duplicates, or even want them, then it's always better to use
UNION ALL instead of
UNION, as that avoids a potentially costly sort or hash operation to remove the duplicates.
SELECT 'Book' AS OBJECT_TYPE, ID FROM BOOK UNION ALL -- No removal of duplicates necessary in this case SELECT 'Author' AS OBJECT_TYPE, ID FROM AUTHOR;