Hi, why use UNION but not use CREATE TABLE and INSERT INTO?

hi, why we not use CREATE TABLE and INSERT INTO? for new table?

lesson: https://www.codecademy.com/paths/data-science/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/lessons/churn-rates-les/exercises/multi-month-i

with months as
(
SELECT
‘2017-01-01’ AS first_day,
‘2017-01-31’ AS last_day
union
SELECT
‘2017-02-01’ AS first_day,
‘2017-02-28’ AS last_day
union
SELECT
‘2017-03-01’ AS first_day,
‘2017-03-31’ AS last_day)
SELECT *
FROM months;

You can use CREATE TABLE and INSERT INTO. But I THINK that this will consume more memory and space in your SQL database, and a temporary table (Using WITH) doesn’t waste it.

2 Likes

WITH doesn’t create a temporary table, it creates a CTE (Common Table Expression).

You’re on the right track, though. A CTE only exists within the scope of the query that defines it, so once your query has finished executing the CTE no longer exists.

In contrast, I think most SQL engines keep temporary tables around for the duration of the session so the table exists until you disconnect from the server.

Which one you’d use, i.e. CTE vs temp table, would depend on how long you’d need the resulting data.

1 Like