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
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
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!