CODEWITHSUNDEEP
mysqlsqlselectunion
bearfriend
bearfriend

Reputation: 10421

Add "field" of table name to SQL results from UNION?

If I have a simple union

select name, phone from users union select name, phone from vendors;

Is there a way to add the table name to the results?

So instead of

+--------+-------+
| name   | phone |
+--------+-------+
| Jim    | 123...|
| Macy's | 345...|
+--------+-------+

I'd get

+--------+-------+---------+
| name   | phone | table   |
+--------+-------+---------+
| Jim    | 123...| users   |
| Macy's | 345...| vendors |
+--------+-------+---------+

Upvotes: 11

Views: 18346

Answers (1)

roman
roman

Reputation: 117400

select name, phone, 'users' as table_name from users
union
select name, phone, 'vendors' as table_name from vendors;

Better solution will be to use union all, so server will not be checking for distinct values

select name, phone, 'users' as table_name from users
union all
select name, phone, 'vendors' as table_name from vendors;

Upvotes: 31

Related Questions