SQL: Creating Tables using CTEs

Question

I am working with Netezza SQL.

I have the following SQL code:

create table sample1(
var1 integer,
var2 integer
);

insert into sample1(var1,var2)
values(1,1);
insert into sample1(var1,var2)
values(2,2);


create table sample2(
var1 integer,
var2 integer
);

insert into sample2(var1,var2)
values(3,3);
insert into sample2(var1,var2)
values(4,4);


select * from sample1
union all
select * from sample2;

My Question: Is it possible to combine this SQL into a single statement that runs start to finish (e.g. CTE based)? The reason I am interested in this because I don't have to delete tables sample1, sample2 after.

Is it possible to do this?

Thanks!

Answer 1

I'm not much familiar with Netezza, but most database products have some variant of table-value constructors. In SQL Server, it would let you do something like this:

with sample1 as (
    SELECT * 
    FROM (VALUES 
        (1, 1),
        (2, 2)
    ) s(var1, var2)
),
sample2 as (
    SELECT *
    FROM (VALUES
        (3, 3),
        (4, 4)
    ) s(var1, var2)
)
select * from sample1
union all
select * from sample2;

https://dbfiddle.uk/Ngun8G5R

MySQL would look almost the same, but it needs the row keyword added before each tuple. Again, I'm not sure what the Netezza variant will look like. I did find a reference indicating Netezza is based on Postgresql, in which case the code above works at least as far back as Postgresql 9.3.

But I'm struggling to see how this is a good idea relative to just having the whole thing as a single table-value constructor or (better yet) actually creating a real table (not a temp table) to hold this data.

Answer 2

What Joel San has written is almost correct

You can define sample1 and sample2 as CTEs and then union the results of the two. Here's how you can do that:

WITH sample1 AS (
    SELECT 1 AS var1, 1 AS var2
    UNION ALL
    SELECT 2, 2
),
sample2 AS (
    SELECT 3 AS var1, 3 AS var2
    UNION ALL
    SELECT 4, 4
)

SELECT * FROM sample1
UNION ALL
SELECT * FROM sample2;