Select from multiple identical tables with origin

Question

What I am trying to archive is getting data from similar tables (the column structure is the same) but also returning a column table_origin allowing to indentify the table afterwards.

I've tried to get something like this working in MySQL, but had no success so far.

Probably something like this (not working) example:

SELECT * FROM (
    SELECT *, 44 as table_origin FROM `CustomerData_44`
) AS a FULL OUTER JOIN (
    SELECT *, 58 as table_origin FROM `CustomerData_58`
) AS b ON true;

Answer 1

When you need all values, use UNION ALL

Statement UNION makes DISTINCT

https://dev.mysql.com/doc/refman/5.5/en/union.html

What is the difference between UNION and UNION ALL?

SELECT *, 44 as table_origin FROM `CustomerData_44`
UNION ALL
SELECT *, 58 as table_origin FROM `CustomerData_58`

Answer 2

If you're trying to fetch rows from both of these tables with table_origin as identifier column then you can try with UNION of multiple SELECT statements instead of using the join's.

SELECT *, 44 as table_origin FROM `CustomerData_44`
UNION
SELECT *, 58 as table_origin FROM `CustomerData_58`

Each SELECT statement differs for the table being selected from and adds a column table_origin to the result, so you can identify it later.

When you need to include duplicate values, you can use UNION ALL instead of UNION:

The default behavior for UNION is that duplicate rows are removed from the result. The optional DISTINCT keyword has no effect other than the default because it also specifies duplicate-row removal. With the optional ALL keyword, duplicate-row removal does not occur and the result includes all matching rows from all the SELECT statements.

However, in the case above we add a label field to the subqueries, and as long as the labels are different, we can only encounter duplicate rows if there already are duplicates in the source tables. If you have a unique non-null column (like the auto-incrementing id often) that can not happen.