SQL UNION / UNION ALL
Introduction
UNION keyword in SQL is used to combine to SELECT statement results with out any duplicate. In order to use UNION and combine results both SELECT statement should have same number of column with same data type in same order, but the length of column can be different.
Syntax
- SELECT column_1 [, column_2 ] FROM table_1 [, table_2 ] [WHERE condition]
UNION | UNION ALL
SELECT column_1 [, column_2 ] FROM table_1 [, table_2 ] [WHERE condition]
Remarks
UNION and UNION ALL clauses combine the result-set of two or more identically structured SELECT statements into a single result / table.
Both the column count and column types for each query have to match in order for a UNION / UNION ALL to work.
The difference between a UNION and a UNION ALL query is that the UNION clause will remove any duplicate rows in the result where the UNION ALL will not.
This distinct removal of records can significantly slow queries even if there are no distinct rows to be removed because of this if you know there wont be any duplicates (or don't care) always default to UNION ALL for a more optimised query.
