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

Question

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

Edit your question and provide sample data and desired results. — Gordon Linoff, Feb 06 '18 at 13:04
you are looking for join I believe?.. do you want a cartesian product of two, or combined values somehow?.. — Vao Tsun, Feb 06 '18 at 13:04
How do you want to choose which rows to line up from each table? A few rows of sample input ***and*** output data resolves this ambiguity. — Disillusioned, Feb 06 '18 at 13:12

Disillusioned · Accepted Answer · 2018-02-06T14:14:12.893

2

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.

edited Feb 06 '18 at 14:14

answered Feb 06 '18 at 13:16

Disillusioned

14,635
3
43
77

1

adding `COALESCE(t1.id, t2.id) as id, `would make it complete. – joop Feb 06 '18 at 14:09

score 0 · Answer 2 · answered Feb 06 '18 at 13:05

0

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;

answered Feb 06 '18 at 13:05

Gordon Linoff

1,242,037
58
646
786

What is the different between **table1** and **t1** in from? Aren't both the table name? And therefore only once needed? – N.Varela Feb 06 '18 at 13:16
1

@N.Varela `t1` and `t2` are aliases defined in the `FROM` clause. You don't need them because you could write `table1.attr1 as attr1_1` etc. But the aliases reduce the code needed to disambiguate the source table. – Disillusioned Feb 06 '18 at 13:19

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

2 Answers2