11

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 |
+--------+-------+---------+
bearfriend
  • 10,322
  • 3
  • 22
  • 28

1 Answers1

31
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;
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
  • Oh wow, I actually thought of that but figured it was way too simple. Thanks! – bearfriend Oct 20 '12 at 20:24
  • The difference would be if a user and vendor had the same info, I'd get both results with distinct table_name? Or am I interpretting UNION ALL wrong? – bearfriend Oct 20 '12 at 20:33
  • Thank you @MartinSmith, yes, it's actually better. When you're using `union` server will try to give you only distinct results. Actually, it will be the same resultset, but it's better not to bother server with extra work :) – Roman Pekar Oct 20 '12 at 20:35
  • 1
    @user1484099 - The different `table_name` means there definitely won't be any duplication between the two sets. If you want to remove duplication within either set you would be better off using `DISTINCT` on the relevant individual queries and combining them with `UNION ALL` – Martin Smith Oct 20 '12 at 20:42
  • I don't actually want to use DISTINCT in this case, but thank you, that's good information. – bearfriend Oct 25 '12 at 19:44
  • 1
    I found this answer to part of my question but have an added twist: I have about 120 tables that I want to merge and don't want to type the names of each table. Even better, I would like to create a look-up table with a simple integer key that identifies the original table name and then simply add a foreign key column to the large table of merged rows. – Land Surveyor Feb 25 '16 at 00:13