Do the tables have to have the same number of columns when combining them?

Question

In the context of this exercise, do tables have to have the same number of columns when combining them?

Answer

Yes, when combining two tables using a UNION, they must have the same number of columns. The columns do not need to have the same name, because they are combined based on column positions rather than the column names. However, they should have similar data types.

9 Likes

I just managed to get in to this stage of the course.

Regarding the answer to this question as tables must have the same amount of columns, I tired WITH statement to create temporary tables with the columns I want to select. Then I just used UNION to merge them both.

Basically, if tables do not have the same amount of columns and you want to use UNION you can first create temporary tables with WITH statement to select the amount of columns you want. I hope this helps!.

WITH songs_test AS (
SELECT id, title, year
FROM songs
),
bonus_songs_test AS (
SELECT id, title, year
FROM bonus_songs
)
SELECT *
FROM songs_test
UNION
SELECT *
FROM bonus_songs_test
LIMIT 10;

16 Likes

What do I do if I want to limit table1 (songs table in this case) and display all of the rows of table2 (bonus_songs table in this case)?

I am asking this because when I use LIMIT after doing the UNION, I do not know if the bonus_songs rows have been added. I don’t want to display the entire table and scroll all the way down to see if the UNION has taken place.

That’s really Nice. Thankyou @khanster321 for sharing such Great Information.

@khanster321 This is Brilliant !

@coffeencake Can you please give some insights to this ?

@theskygupta @nayanreddy3217184308

Good questions. If you want to limit table1 and display all the rows of table2, you can do this:

WITH new_table AS (
  select * from table1
  limit 10
)
select * from table2
union
select * from new_table;

It is always good practice to validate your query somehow. In the example exercise, I checked the total records in the table first and then tested my union expecting 14 rows but only received 10 rows.

--
-- Check the total number of records in the table first.
--
select count(*) from bonus_songs        -- 4 rows

select count(*) from songs              -- 2229 rows

-- This query will only return a max of 10 rows for the two tables combined
select * from bonus_songs
union
select * from songs
limit 10;

-- Create a temporary table with the subset of date from songs.
WITH new_songs AS (
  select * from songs
  limit 10
)

I tried to breakdown my thought process - I hope that helps.

2 Likes

Thank You @coffeencake :grin:

1 Like