How to select data from two identical tables?

[mameha1977]

I have some user records stored in two tables.

The table structure is identical.

table1 = latest data
table2 = archived data

(once a month the latest data is added to the archived data).

Question: How to select data from BOTH tables at once?

I tried this but it failed:

SELECT `lastname` , `firstname` , `email`
FROM `table1` , `table2`
WHERE `country` = 'CN'

[mattyblah]

select * from table1
union
select * from table2

if there are no duplicate records you can use union all as it is faster but doesn't remove duplicate rows

[mameha1977]

works great, thanks!

[Mr Initial Man]

You can also identify rows as belonging to specific tables, then join the two on a row. For example, I'll modify your query to show you how this would look.


SELECT table1.lastname, table1.firstname, table1.email FROM table1 JOIN table2 ON table1.country = table2.country WHERE table1.country = 'CN'


It returns fewer redundant results, which is easier on your server.