select from union of two tables

Question

I have two tables Cars2016 and Cars2015, which have exactly the same columns.

I want to simply concatenate these two tables to create this kind of query :

SELECT Mark, COUNT(Models) FROM (Cars2015 UNION Cars2016) GROUP BY Mark

This query aims to know for each mark how many models it has, cumulating year 2015 and year 2016.

How can I do that efficently ?

Thanks !

Answer 1

Probably the most efficient way is to count in each table, then union that and sum them.

SELECT Mark, SUM(count) AS count
FROM (
    SELECT Mark, COUNT(*) AS count
    FROM Cars2015
    GROUP BY Mark
    UNION ALL
    SELECT Mark, COUNT(*) AS count
    FROM Cars2016
    GROUP BY Mark
) AS u
GROUP BY Mark

If you have an index on Mark in the tables, the subqueries should be very efficient. Then it's doing a union of relatively small intermediate tables that contain the aggregates, and then combining them.

Another option is to use the MERGE storage engine, which allows you to create a virtual table that contains the union of other tables:

CREATE TABLE Cars (
    -- column names here
) ENGINE=MERGE UNION=(Cars2015, Cars2016);

SELECT Mark, COUNT(*)
FROM Cars
GROUP BY Mark;

Answer 2

You need to union the 2 tables in the from clause with a subquery:

select mark, count(*)
from
    (select models, mark from Cars2015
     union all
     select models, mark from Cars2016) t
group by mark

If you would like to get the overall count only (you do not have the mark field in the select list), then I would use 2 subueries in the select list and add them up:

select (select count(models) from Cars2015) + (select count(models) from Cars2016) as total_models