I want two combine two tables in a query where the resulting table has an extra type column to specify which table the value was taken from:
table1
| id | name |
|---|---|
| 1 | name1 |
| 2 | name2 |
table2
| id | name |
|---|---|
| 3 | name3 |
| 4 | name4 |
result
| table | id | name |
|---|---|---|
| table1 | 1 | name1 |
| table1 | 2 | name2 |
| table2 | 3 | name3 |
| table2 | 4 | name4 |
How to achieve that?
CodePudding user response：
Use a union query:
SELECT 'table1' AS [table], id, name FROM table1
UNION ALL
SELECT 'table2', id, name FROM table2
ORDER BY id;
CodePudding user response：
Yes the union query is a quick solution and if don't want any duplicate you use simply UNION, without ALL