I tried to make a new table,c, renaming user1 to user2, user2 to user 1 and UNION ALL the original table.

WITH c AS 
(
    SELECT 
        user1 AS user2, user2 AS user1, msg_count 
    FROM f
) 
SELECT * 
FROM c 
UNION ALL 
(SELECT user1, user2, msg_count 
 FROM f)

This is f, the original table:

The question is: why am I not getting a rename-columned table to UNION? My query returned a UNION of two identical original tables

The output is this:

which is not what I expected, I was expecting this instead:

Could someone please shed some light on this?

Thanks a lot!

CodePudding user response：

There is no need for a CTE

You only need to switch the columns around for user1 and two:

(SELECT user1 AS user2, user2 AS user1, msg_count FROM f)
UNION ALL (SELECT user2, user1, msg_count FROM f)

Your query adds the same result twice, but as I already wrote the second query must have the user1 and user2 reversed, to get your result

CodePudding user response：

To explain the issue, consider the output of just selecting from your CTE - you have renamed the columns, but the order (left to right) remains unchanged, so now think about select *...

Your desired results rely on you selecting the correct columns, not their order:

with c as (select user1 as user2, user2 as user1, msg_count from f) 
select user1, user2, msg_count
from c 
union all (select user1, user2, msg_count from f);