UNION vs UNION ALL
UNION returns only distinct values. The UNION operator eliminate duplicate rows.
UNION ALL returns all values. The UNION ALL operator will not eliminate duplicate rows.
Students table:
| ID | NAME | CITY |
|---|---|---|
| 1 | Emma | New York |
| 2 | Daniel | Chicago |
| 3 | Joseph | Dallas |
UNION Example
select id, name, city
from Students where id=1
union
select id, name, city
from Students where id=1;
Results
| ID | NAME | CITY |
|---|---|---|
| 1 | Emma | New York |
UNION ALL Example
select id, name, city
from Students where id=1
union all
select id, name, city
from Students where id=1;
Results
| ID | NAME | CITY |
|---|---|---|
| 1 | Emma | New York |
| 1 | Emma | New York |