CODEWITHSUNDEEP
sqlpostgresql
user23899
user23899

Reputation: 101

Setting Column to Null and Joining in Postgresql

So I have two tables. One table contains two columns. The second table contains one column. However the column in the second table matches the column name of the first column of the first table:

Table 1:
a       b
hello | 6
world | 7

Table 2:
a
new
t1

I want to join these two tables to result in:

Table 3:
a         b
hello  | 6
world  | 7
new    | Null
t1     | Null

I have looked into Cross Join, and many other Joins but none of them seem to help in this case.

Upvotes: 0

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

UNION ALL is the right answer. If you insist on using JOIN, you could do:

select coalesce(t1.a, t2.a) as a, t1.b
from t1 full outer join
     t2
     on 1 = 0;

Upvotes: 0

marcothesane
marcothesane

Reputation: 6749

SELECT a, b FROM table_1
UNION ALL
SELECT a, NULL::INTEGER FROM table_b
;

With some other databases, the cast to integer as above is not necessary.

Upvotes: 3

Related Questions