UNION and UNION ALL-
when 2 select statements have the sames set of columns, the rows from the 2 select statements can be appended one after another.
The difference being that UNION remove duplicate rows, while UNION ALL does not. Another difference between them is that UNION ALL is faster than UNION but may look slow because it returns more data which takes more time to travel via network.
This keyword is very well supported by all major database e.g. Oracle, Microsoft SQL Server, MySQL and PostgreSQL. To do this in SQL Server, it must build a temporary index on all the columns returned by both queries. If the index cannot be build for the queries, you will get a SQL error. In this situation, UNION ALL may be the solution if you don’t have rights to do this. Alternatively, you may need to use a temporary table with INSERT queries to store the results of each query and use a standard SELECT query to group and get the unique values.
Let us delve in more detail:
SQL UNION /UNION ALLSyntax
SELECT column_name(s) FROM table1
UNION <ALL>
SELECT column_name(s) FROM table2;