SQL- combining two tables in SQL

Question

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.

Oracle 11g database

Shubham Goyal
– Shubham Goyal

2016-05-31 11:33:49 +00:00
Commented May 31, 2016 at 11:33 — Shubham Goyal
– Shubham Goyal, Commented May 31, 2016 at 11:33

Gordon Linoff · Accepted Answer · 2016-05-31 11:41:31Z

1

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).

edited May 31, 2016 at 11:41

answered May 31, 2016 at 11:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shubham Goyal Over a year ago

Worked perfectly!! Can you tell the solution if there are many tables and I want to combine them in the same way

Gordon Linoff Over a year ago

@ShubhamGoyal . . . Assuming that you have a master table with all the rows, then you would just keep adding left joins for each table.

Giorgos Betsos · Accepted Answer · 2016-05-31 11:33:21Z

1

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

answered May 31, 2016 at 11:33

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Comments

user330315 · Accepted Answer · 2016-05-31 11:32:20Z

0

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);

answered May 31, 2016 at 11:32

user330315

2 Comments

Shubham Goyal Over a year ago

Thanks!! It is working fine. Can you tell me the solution where there are multiple tables and I want to combine them in the same way.

user330315 Over a year ago

Just add similar join conditions for the additional tables

Collectives™ on Stack Overflow

SQL- combining two tables in SQL

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related