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

user_19695163 0
Viewed 34

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?

postgresql sql
2 Answers

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.

user_124928900

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

user_35576760
Related