CODEWITHSUNDEEP
sqlpostgresql
N.Varela
N.Varela

Reputation: 910

How to combine/merge two select queries by COLUMN in PostgreSQL?

I would like to combine/merge two select queries by COLUMN and not by rows as shown here with UNION.

Let`s say I have 

SELECT attr1, attr2 FROM table1

and 

SELECT attr1, attr2 FROM table2

Note, that both tables have the same Attribute Names.

And I want a result with following columns:

attr1_table1, attr2_table1, attr1_table2, attr2_table2

Edit (just for the next as the outer join worked fine):

My first Select Returns something like:

id  attr1  attr2
1   3      5
2   4      6

and my second select

id  attr1 attr2
1   7     9
2   8     10

And my desired result is:

id  attr1 attr2 attr1 attr2
1   3     5     7     9
2   4     6     8     10

Thanks

Upvotes: 2

Views: 3557

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

A cross join would be very dangerous because it produces a huge amount of data. E.g. if Table1 and Table2 each have 1000 rows, the result of a cross join would be 1,000,000 rows!

However, I assume you want to line up matching rows. So use the following:

select  COALESCE(t1.id, t2.id) as id,
        t1.attr1, t1.attr2, t2.attr1, t2.attr2
from    Table1 t1
        full outer join Table2 t2 on
            t2.id = t1.id

The full outer join means this also returns rows where no match is found.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your question is rather vague. But one possible answer is a cross join:

select t1.attr1 as attr1_1, t1.attr2 as attr2_1,
       t2.attr1 as attr1_2, t2.attr2 as attr2_2
from table1 t1 cross join
     table2 t2;

Upvotes: 0

Related Questions