Menu
What is the difference between UNION and UNION ALL?
801
The only difference between Union and Union All is that Union extracts the rows that are being specified in the query while Union All extracts all the rows including the duplicates (repeated values) from both the queries.
UNION ALL is faster and more optimized than UNION. But we cannot use it in all scenarios. UNION ALL with SELECT DISTINCT is not equivalent to UNION.
Both UNION and UNION ALL operators combine rows from result sets into a single result set. The UNION operator removes eliminate duplicate rows, whereas the UNION ALL operator does not. Because the UNION ALL operator does not remove duplicate rows, it runs faster than the UNION operator.
UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE)
SELECT 'foo' AS bar UNION SELECT 'foo' AS bar Result:
+-----+ | bar | +-----+ | foo | +-----+ 1 row in set (0.00 sec) SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar Result:
+-----+ | bar | +-----+ | foo | | foo | +-----+ 2 rows in set (0.00 sec) 
answered Oct 12 '22 14:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With