# Combine multiples table to a new one

I apologize, I am not sure how to word this question. It probably has been answered somewhere, but I couldn't find it if so.

I have three tables that I want to combine in one using a common `id` column, with empty rows here and there.

### table1

`````` id | col1
----|------
1 | 23.4
3 | 13.4
4 |  7.6
``````

### table2

`````` id | col2
----|------
1 | 12.2
2 | 17.1
``````

### table3

`````` id | col3
----|------
1 | 22.2
2 | 14.1
5 | 10.8
``````

I want to create a fourth table table4:

`````` id | col1 | col2 | col3
----|------|------|------
1 | 23.4 | 12.2 | 22.2
2 | NULL | 17.2 | 14.1
3 | 13.4 | NULL | NULL
4 | 7.6  | NULL | NULL
5 | NULL | NULL | 10.8
``````

I'm stumped as to how to approach this problem honestly.

• You need to use `full outer join`s. Commented Dec 4, 2019 at 7:01

I think you are looking for a `FULL OUTER JOIN`:

``````SELECT COALESCE(a.id,b.id,c.id), COALESCE(a.col1,'NULL'), COALESCE(b.col2,'NULL'), COALESCE(c.col3,'NULL')
FROM table1 a
FULL OUTER JOIN table2 b ON a.id=b.id
FULL OUTER JOIN table3 c ON b.id=c.id;
``````

or, if you want blanks instead of strings:

``````SELECT COALESCE(a.id,b.id,c.id), a.col1, b.col2, c.col3
FROM table1 a
FULL OUTER JOIN table2 b ON a.id=b.id
FULL OUTER JOIN table3 c ON b.id=c.id;
``````
• If an ID exists in `table1` and `table3` but not `table2`, you will end up having two rows with that ID in the output, because the `table3` joining condition doesn't take that case into account. Instead of just `b.id = c.id` you could use something like `COALESCE(a.id, b.id) = c.id`, or maybe `c.id IN (a.id, b.id)`. Commented Dec 4, 2019 at 9:02