CODEWITHSUNDEEP
sqloracle-databasejoin
Shubham Goyal
Shubham Goyal

Reputation: 667

SQL- combining two tables in SQL

I have one table as follows which has data only for 2011 and 2012:

country     year    value1

ABCD        2011     x1
ABCD        2012     x2
PQRS        2011     x3
PQRS        2012     x4

and another table as follows which has data for years from 2010 to 2014:

country     year    value2

ABCD        2010     y1
ABCD        2011     y2
ABCD        2012     y3
ABCD        2013     y4
ABCD        2014     y5
PQRS        2010     y6
PQRS        2011     y7
PQRS        2012     y8
PQRS        2013     y9
PQRS        2014     y10

I want a combined table as follows:

country     year    value2   value1

ABCD        2010     y1       null
ABCD        2011     y2       x1
ABCD        2012     y3       x2
ABCD        2013     y4       null
ABCD        2014     y5       null
PQRS        2010     y6       null
PQRS        2011     y7       x3
PQRS        2012     y8       x4
PQRS        2013     y9       null
PQRS        2014     y10      null

Can anybody suggest a way? I both the cases primary key is (country+year). Also if many such tables are there what might be the solution?

Thanks.

Upvotes: 0

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use left join:

select t2.*, t1.value1
from table2 t2 left join
     table1 t1
     on t2.country = t1.country and t2.year = t1.year;

If the second table did not repeat the rows from the first table, then a full outer join (or some sort of union) would be necessary. However, given the data in the question, a left join is quite sufficient (and should have better performance).

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

What you are looking for is a FULL OUTER JOIN:

SELECT COALESCE(t1.country, t2.country) AS country,
       COALESCE(t1.year, t2.year) AS year
       t1.value1, t2.value2 
FROM table1 AS t1
FULL OUTER JOIN table2 AS t2
ON t1.country = t2.country AND t1.year = t2.year

Upvotes: 1

user330315
user330315

Reputation:

Looks like you want a full outer join: 

select country, year, t1.value1, t2.value2
from table_1 t1
  full outer join table_2 t2 using (country, year);

Upvotes: 0

Related Questions