The SQL UNION ALL query is similar to SQL UNION query, it’s also used to merge two or more SQL SELECT query results. The difference is that SQL UNION returns distinct values, while SQL UNION ALL returns ALL the values.
SQL UNION ALL Query Syntax
SELECT Column(s) FROM Table1 UNION ALL SELECT Column(s) FROM Table2
SQL UNION ALL Query Example
Table: FinanceEmployees
EmployeeId | FirstName | LastName |
---|---|---|
203 | Kilooa | Joooo |
204 | Hioiaaa | Hifa |
205 | Yuiofa | Iioooa |
206 | Miofa | Poqqq |
EmployeeId | FirstName | LastName |
---|---|---|
206 | Miofa | Poqqq |
207 | Qifaww | Siafoa |
208 | Ziooo | Rrroo |
209 | UUifad | Bbiiw |
Select all the employees in FinanceEmployees and DevelopmentEmployees:
SELECT EmployeeId, FirstName, LastName FROM FinanceEmployees UNION ALL SELECT EmployeeId, FirstName, LastName FROM DevelopmentEmployees
The result will look like:
EmployeeId | FirstName | LastName |
---|---|---|
203 | Kilooa | Joooo |
204 | Hioiaaa | Hifa |
205 | Yuiofa | Iioooa |
206 | Miofa | Poqqq |
206 | Miofa | Poqqq |
207 | Qifaww | Siafoa |
208 | Ziooo | Rrroo |
209 | UUifad | Bbiiw |
We can see that the result-set has duplicated rows.