CODEWITHSUNDEEP
sqlpostgresql
nachocab
nachocab

Reputation: 14364

How to join and union at the same time

I'm sure this has a simple solution but I'm struggling to find it.

I have two tables

CREATE TABLE t1 (
  "name" VARCHAR(1),
  "id" INTEGER,
  "data1" VARCHAR(2)
);

INSERT INTO t1
  ("name", "id", "data1")
VALUES
  ('a', '1', 'a1'),
  ('d', '1', 'd1');
  
  

CREATE TABLE t2 (
  "name" VARCHAR(1),
  "id" INTEGER,
  "data2" VARCHAR(2)
);

INSERT INTO t2
  ("name", "id", "data2")
VALUES
  ('d', '1', 'd2'),
  ('k', '1', 'k2');

I want this final combined table:

| name | id  | data1 | data2 |
| ---- | --- | ----- | ----- |
| a    | 1   | a1    |       |
| d    | 1   | d1    | d2    |
| k    | 1   |       | k2    |

Things I've tried:

Do a union

select 
  t1.name,
  t1.id,
  t1.data1,
  NULL as data2
from t1
union 
select 
  t2.name,
  t2.id,
  NULL as data1,
  t2.data2
from t2

| name | id  | data1 | data2 |
| ---- | --- | ----- | ----- |
| a    | 1   | a1    |       |
| d    | 1   | d1    |       |
| d    | 1   |       | d2    |
| k    | 1   |       | k2    |

Do a full join

select * from t1
full join t2 on t2.id = t1.id 
     and t2.name = t1.name;

| name | id  | data1 | name | id  | data2 |
| ---- | --- | ----- | ---- | --- | ----- |
| a    | 1   | a1    |      |     |       |
| d    | 1   | d1    | d    | 1   | d2    |
|      |     |       | k    | 1   | k2    |

The answer is somewhere in between 😅

Upvotes: 0

Views: 117

Answers (3)

nachocab
nachocab

Reputation: 14364

Thanks, Gordon and Nick for your answers.

I checked the documentation and found an even shorter variation:

select * 
from t1 
natural full join t2;

From the documentation

Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.

Upvotes: 1

Nick
Nick

Reputation: 147146

You're looking for a FULL OUTER JOIN:

SELECT COALESCE(t1.name, t2.name) AS name,
       COALESCE(t1.id, t2.id) AS id,
       t1.data1,
       t2.data2
FROM t1
FULL OUTER JOIN t2 ON t2.name = t1.name

Output:

name    id  data1   data2
a       1   a1      null
d       1   d1      d2
k       1   null    k2

Demo on db-fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you just want a full join:

select *
from t1 full join
     t2
     using (name, id);

Upvotes: 1

Related Questions