Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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.
For example:
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;
Feedback
Do you have any feedback about this page? We'd love to hear it!