We have some legacy tables with very similar structure. These tables were kept separate because the format of the same information was slightly different, depending upon the source from where the information was scraped [don't shoot the schema inheritor;)]. We have been using UNION in our queries.
For example:
SELECT a, b, c, FROM Table_1 WHERE …
UNION
SELECT a, b, c, FROM Table_2 WHERE …
UNION
SELECT a, b, c, FROM Table_3 WHERE …
This kludgy arrangement has been working but we would like to know which table a piece of information in our output is coming from. Is there some way to realize that?
Just add a column to each subquery that contains the table name:
SELECT 'Table_1' tablename, a, b, c, FROM Table_1 WHERE ...
UNION ALL
SELECT 'Table_2', a, b, c, FROM Table_2 WHERE ...
UNION ALL
SELECT 'Table_3', a, b, c, FROM Table_3 WHERE ...
Note that I changed the UNIONs to UNION ALLs, since adding this column basically defeats the deduplication functionality that UNION provides.
You can do:
SELECT 'Table_1' as source, a, b, c, FROM Table_1 WHERE …
UNION ALL
SELECT 'Table_2', a, b, c, FROM Table_2 WHERE …
UNION ALL
SELECT 'Table_3', a, b, c, FROM Table_3 WHERE …
And don't forget to use UNION ALL instead of UNION. It has better performance and doesn't remove rows.