Basic Sentence

1
2
3
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
  • The SELECT statements within a UNION must have the same number of columns. Columns must also have similar data types. Meanwhile, the order of the columns in each SELECT statement must be the same.
  • SELECT will automatic deduplication, while SELECT ALL will not.

UNION


Tables:

employees:

emp_id name dpt_id
1 apple 101
2 boy 102
3 cat 101
4 dog 103

former_employees:

emp_id name dpt_id
5 egg 102
2 boy 102 
1
2
3
SELECT name, dpt_id FROM employees
UNION
SELECT name, dpt_id FROM former_employees;

result:

name dpt_id
apple 101
boy 102 automatic deduplication
cat 101
dog 103
egg 102

UNION ALL

Tables: 

1
2
3
SELECT name, dpt_id FROM employees
UNION ALL
SELECT name, dpt_id FROM former_employees;

result:

name dpt_id
apple 101
boy 102 first record
cat 101
dog 103
egg 102
boy 102 from former_employees