CODEWITHSUNDEEP
mysqlsqlselectmariadbunion
Sandeep
Sandeep

Reputation: 1401

MySQL> Getting table name along with SELECT output

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?

Upvotes: 1

Views: 122

Answers (2)

The Impaler
The Impaler

Reputation: 48810

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.

Upvotes: 3

GMB
GMB

Reputation: 222512

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.

Upvotes: 4

Related Questions