UNION ALL vs UNION Oracle PL/SQL

by Andy
Posted on March 3, 2018May 20, 2021

UNION ALL vs UNION Oracle PL/SQL

You might be wondering should I use UNION or UNION ALL in a SQL query. What is the performance difference in UNION ALL vs UNION? This should shed some light on which one is appropriate to use in your SQL query.

UNION

In Oracle the UNION clause delays returning any rows until each portion of the UNION is sorted and merged and duplicates filtered out.

SELECT acct, balance
FROM debit
WHERE trandate = '31-DEC-95'
UNION 
SELECT acct, balance
FROM credit
WHERE trandate = '31-DEC-95';

SELECT acct, balance

FROM debit

WHERE trandate = '31-DEC-95'

UNION

SELECT acct, balance

FROM credit

WHERE trandate = '31-DEC-95';

UNION ALL

A UNION ALL simply returns all rows without the overhead of sorting, merging and removing duplicates from the results. Typically if you don’t care about duplicates or the tables are mutually exclusive you can use UNION ALL to make a dramatic increase performance of the query.

SELECT acct, balance
FROM debit
WHERE trandate = '31-DEC-95'
<strong>UNION ALL</strong>
SELECT acct, balance
FROM credit
WHERE trandate = '31-DEC-95';

SELECT acct, balance

FROM debit

WHERE trandate = '31-DEC-95'

<strong>UNION ALL</strong>

SELECT acct, balance

FROM credit

WHERE trandate = '31-DEC-95';

If performance is more important than the duplicates than UNION ALL will speed up the query, otherwise stick with UNION .