how to merge columns of two tables in SQL to form one final table with all columns?-CodePudding

In redshift, I have table A with 200 columns and Table B with 300 columns.Both these tables have only 2 common column. I want to create final table with all columns of A & B with common columns present only once. Is there easy way to do it ?

CodePudding user response：

To avoid the common columns appearing twice, use:

CREATE TABLE combined AS
(
SELECT *
FROM A
JOIN B USING (common_col1, common_col2)
)

CodePudding user response：

Try a simple join on those 2 matching columns You could use these 2 columns as Diststyle and Sortkey to improve the performance too on redshift as DISTKEY(col1, col2) and SORTKEY(col1, col2) for both the tables.

    Create table final as (
    Select * from A Join B
    On A.col1 =B.col1 and A.col2=B.col2) ;