How to select table names with UNION results?

Question

Here are the results with UNION, but I also want to know the source table associated with each row:

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

The actual result of the query is this:

city
572
123

But I'd like this result instead:

city tablet_result
572 Customers
123 Suppliers

duplicate http://dba.stackexchange.com/questions/76188/get-table-name-from-union-query — Jeff Puckett, Apr 23 '16 at 19:55

score 3 · Answer 1 · answered Apr 23 '16 at 19:43

3

SELECT City, 'Customers' as tablet_result FROM Customers
UNION
SELECT City, 'Suppliers' as tablet_result FROM Suppliers

answered Apr 23 '16 at 19:43

yefrem

score 2 · Accepted Answer · edited May 23 '17 at 12:23

2

Ok, try:

SELECT City,'Customers' as tblName FROM Customers
UNION
SELECT City,'Suppliers' as tblName FROM Suppliers
ORDER BY City;

Play with the order by.

Also consider doing a UNION ALL. See this for the difference.

edited May 23 '17 at 12:23

Community

answered Apr 23 '16 at 19:44

Drew

2 Answers2