UNION and UNION ALL command in SQL

If we want to display the results of the two select statements as a whole, we need to use the ‘UNION and UNION ALL keywords.

The function of UNION is to combine multiple results and display them.

SELECT * FROM table_a
UNION/UNION ALL
SELECT * FROM table_b

The UNION and UNION ALL keywords combine two result sets into one, but the two are different in terms of usage and efficiency.

1. Treatment of duplicate results: UNION will filter out duplicate records after table linking, and Union All will not remove duplicate records.

2. Processing of sorting: Union will sort according to the order of the fields; UNION ALL simply merges the two results and returns.

In terms of efficiency, UNION ALL is much faster than UNION, so if you can confirm that the merged two sets do not contain duplicate data and do not require sorting, then apply UNION ALL.

Note: The number of fields in the two SQL statements to be combined must be the same, and the data type of the field must be consistent.

The use of UNION and UNION ALL must ensure that the results of SQL statements have the same number of columns, and the data type of each column is the same. But the column names do not necessarily need to be the same.

For example:

SELECT emp_no,e_name FROM emp
UNION
SELECT dep_no,d_name FROM dept

In leetcode 1555. Bank Account Summary , it will use UNION ALL to extract all the transactions instead of UNION. Because UNION may delete records that have the same digits in (id,amount), while these records have different transaction ids which means they are not the same record.

with t as (select id,sum(total) as total
           from(select paid_by as id, -amount as total from Transactions
                union all
                select paid_to as id, amount as total from Transactions
                ) as t
           group by id
          )
select u.user_id , u.user_name,
       ifnull(sum(t.total+u.credit),u.credit) as credit ,
       if(sum(t.total+u.credit)<0,’Yes’,’No’) as credit_limit_breached
from t right join Users u on t.id=u.user_id
group by u.user_id