We can't see your data so we can't tell you why you are not getting 100,756 rows in your results.
You can check just how much data is on your tables with these queries:
select count(*) from hotel_revenue.`2018`;
select count(*) from hotel_revenue.`2019`;
select count(*) from hotel_revenue.`2020`;
If those numbers add up to 100,756 then you must have some duplicate rows within your tables.
UNION
will automatically remove duplicates. Use
UNION ALL
to get all of the records regardless of duplication.
Couple of other points -
- You are not trying to create a temporary table, you are using a common table expression. The syntax looks fine to me, but this will not work in something like sqlfiddle because it classes that as DDL statements. See reference
MySQL :: MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common Table Expressions)[
^] and
MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.2 CREATE TEMPORARY TABLE Statement[
^]
- Your database design is not optimised. Instead of having a table per year (which means you are going to have to create new tables each year AND update any queries that are run against them), you should have a single table with a column for the year. You will find it much easier to work with!
- When selecting data it is not good practice to use
SELECT *
, you should list the columns you require explicitly. Not only does this help with performance (you only bring back what you need), it protects your query against subsequent schema changes. E.g. Imagine someone adds a new column to the 2020 table to handle some data item that wasn't around back in 2019 or earlier ... your union will break because the queries are bringing back different "shaped" datasets. You only actually want the
arrival_date_day_of_month
column so use
select arrival_date_day_of_month from hotel_revenue.`2018` etc
- Finally - watch out for your typing error in
select arrival_date_day_of_month+ from hoteels+