CODEWITHSUNDEEP
sql
williamscathy825
williamscathy825

Reputation: 195

how do i join 2 tables using sql

I have table 1:

 col1     col2
 a        a1
 a        a2
 a        a3

and table 2:

 col1     col3
 a        a1
 a        a4

I want to join table 1 and table 2 to get table 3:

 col1     col2    col3
 a        a1       a1
 a        a2       null
 a        a3       null
 a        null     a4

how can i get table 3 using a sql join?

Upvotes: 2

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use a full join:

select coalesce(t1.col1, t2.col1) as col1, t1.col2, t2.col3
from table1 t1 full join
     table2 t2
     on t1.col1 = t2.col1 and t1.col2 = t2.col3

Upvotes: 1

Schwern
Schwern

Reputation: 164669

We can get them with a left join and a right join and then union them together.

The left join ensures we get all rows in table1 regardless of whether they have a matching row in table2. And the right join gets all rows in table2 regardless of whether they have a matching row in table1. Then we mash them together with union which will also remove duplicates.

Left join.

select t1.col1, t1.col2, t2.col3
from table1 t1
left join table2 t2
  on t1.col1 = t2.col1
 and t1.col2 = t2.col3

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | a1   | a1   |
| a    | a2   | NULL |
| a    | a3   | NULL |
+------+------+------+

Right join. Note that we must select t2.col1 because t1.col will be null.

select t2.col1, t1.col2, t2.col3
from table1 t1
right join table2 t2
  on t1.col1 = t2.col1
 and t1.col2 = t2.col3

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | a1   | a1   |
| a    | NULL | a4   |
+------+------+------+

Union.

select t1.col1, t1.col2, t2.col3
from table1 t1
left join table2 t2
  on t1.col1 = t2.col1
 and t1.col2 = t2.col3
union
select t2.col1, t1.col2, t2.col3
from table1 t1
right join table2 t2
  on t1.col1 = t2.col1
 and t1.col2 = t2.col3

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | a1   | a1   |
| a    | a2   | NULL |
| a    | a3   | NULL |
| a    | NULL | a4   |
+------+------+------+

It's instructive to run these with select * to see the full contents of each query.

Upvotes: 0

Related Questions