How do I FULL JOIN using 3 tables (2 shared columns among 3 tables; 1 shared column only among 2 of-CodePudding

All tables are under the same schema (named "schema")

Shared columns ("number", "id", "letter") between tables have the same data types

"letter" column is not present in "table_1"

table_1

number	id
1	t1
7	t1
8	t1

table_2

number	id	letter
2	t2	a
3	t2	f
10	t2	h

table_3

number	id	letter
4	t3	b
5	t3	y
9	t3	t

What I want

number	id	letter
1	t1
2	t2	a
3	t2	f
4	t3	b
5	t3	y
7	t1
8	t1
9	t3	t
10	t2	h

What I have tried:

SELECT "number", "id", "table_2"."letter", "table_3"."letter"
FROM schema."table_1"
FULL JOIN schema."table_2"
USING ("number", "id")
FULL JOIN schema."table_3"
USING ("number", "id", "letter")
ORDER BY "number";

What I get:

number	id	letter	letter
1	t1
2	t2	a
3	t2	f
4	t3		b
5	t3		y
7	t1
8	t1
9	t3		t
10	t2	h

How do I get it so that there is one "letter" column instead of two while concatenating the shared (between all 3 tables), "number" and "id" columns?

CodePudding user response：

Instead of joining the three tables, try applying a UNION ALL operations. Since this operation requires same schema for all involved tables, select an additional NULL field for the first table. Then you can optionally order your values by the shared "number" field.

SELECT *, NULL as letter FROM tab1
UNION ALL
SELECT * FROM tab2
UNION ALL
SELECT * FROM tab3
ORDER BY number

Check the demo here.

Note: Tend to use join operations when you need to add more fields (information) to your initial table, instead use union operations when you need same schema. Applying joins when you need unions may cause inefficiencies.

CodePudding user response：

You can use COALESCE function which returns the first of its arguments that is not null;

 SELECT
     "number", 
     "id", 
     coalesce("table_2"."letter", "table_3"."letter") letter
   FROM schema."table_1"
   FULL JOIN schema."table_2" USING ("number", "id")
   FULL JOIN schema."table_3" USING ("number", "id", "letter")
   ORDER BY "number";

db<>fiddle here