Table 1:

Country	Year	Population
USA	2004	100
USA	2005	200
UK	2004	300
UK	2005	400

 

Table 2:

Country	Year	GDP
USA	2005	500
USA	2006	600
UK	2005	700
UK	2006	800

 

Hey  guys, can you please help me with a query to print the below input?

Country Year Population GDP

USA    2004   100            null

USA   2005    200           500

USA   2006    null           600

UK    2004    300           null

UK    2005    400          700

UK    2006    null          800

try this

declare @Table1 table(country varchar(20),year int,population int)
declare @Table2 table(country varchar(20),year int,GDP int)
insert into @table1 
values ('USA',2002,2345),('UK',2011,2345)

insert into @table2 
values ('USA',2002,2345),('AU',2014,2345)


select COALESCE(A.country,B.Country) Country,COALESCE(A.year,B.year) Year,
A.population,B.GDP
from @Table1 A FULL outer JOIN @Table2 B on A.Country=B.Country

Please check following SELECT query,

select 
	coalesce(t1.country,t2.country) country,
	coalesce(t1.year,t2.year) year,
	population,
	gdp
from table1 t1
full outer join table2 t2
on t1.country = t2.country and t1.year = t2.year
order by country, year

Similar to above but returns different number of rows in the resu

Sure. Thank You

Okay. Thanks for the reply.

• Marked as answer by KS2909 11 hours 0 minutes ago
• Unmarked as answer by KS2909 11 hours 0 minutes ago

Okay. Thanks for the reply.

• Marked as answer by KS2909 Friday, May 29, 2015 8:05 PM
• Unmarked as answer by KS2909 Friday, May 29, 2015 8:05 PM

Try this:

DECLARE @Table1 table(country VARCHAR(20), year INT, population INT)
DECLARE @Table2 table(country VARCHAR(20), year INT, GDP INT)
INSERT INTO @table1 (country, year, population) VALUES
('USA', 2004, 100),
('USA', 2005, 200),
('UK',  2004, 300),
('UK',  2005, 400)

INSERT INTO @table2 (country, year, gdp) VALUES
('USA', 2005, 500),
('USA', 2006, 600),
('UK',  2005, 700),
('UK',  2006, 800)

SELECT COALESCE(t1.country,t2.country) AS country, COALESCE(t1.year,t2.year) AS year, t1.population, t2.GDP
  FROM @Table1 t1
    FULL OUTER JOIN @Table2 t2
	  ON t1.country = t2.country
	  AND t1.year = t2.year
 ORDER BY country DESC, year

Thank you for the help.

Thank you.