Difference Between Union and Union All

UNION only selects distinct values. UNION ALL selects all values, including duplicates.

Syntax SQL Statement1 UNION [ALL] SQL Statement2

In order to perform UNION the column in SQL statement1 must match column in SQL statement2

SELECT * FROM employees UNION SELECT * FROM employees2;

ID	Last Name	First Name	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
104	Maines	Brad	crew

 

UNION ALL selects all rows from each table and combines them into a single table.

SELECT * FROM employees UNION ALL SELECT * FROM employees2;

ID	Last Name	First Name	Title
1	Johnson	David	crew
2	Hively	Jessica	crew
9	Hicks	Freddy	crew
10	Harris	Joel	crew
11	Davis	Julie	manager
101	Yazzow	Jim	crew
102	Anderson	Craig	crew
103	Carlson	Kevin	crew
11	Davis	Julie	manager
104	Maines	Brad	crew