How do I create temp table using union operation

Question

0.00/5 (No votes)

See more:

SQL

with hoteels as (
					SELECT * FROM hotel_revenue.`2018`
					union 
					SELECT * FROM hotel_revenue.`2019`
					union 
					SELECT * FROM hotel_revenue.`2020`)
                
select arrival_date_day_of_month+ from hoteels

What I have tried:

Here, hotel_revenue is the schema name and 2018,2019, 2020 is tablename. I have two question. 1. I am supposed to get  100,756 rows after union operation.But , I am getting 66,708 rows. Why do I am getting less rows?
2. Failing to create temporary table using union operation Is it possible to create temp table using union?.
I am using mysql dbms.

Posted 24-Oct-22 6:22am

Abrar M 2022

Updated 24-Oct-22 7:59am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-10-24T07:59:00

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:

SQL

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

SQL

select arrival_date_day_of_month from hotel_revenue.`2018` etc

- Finally - watch out for your typing error in

SQL

select arrival_date_day_of_month+ from hoteels+