Sql join in two tables and return empty tab as Unavaliable

In a SQL join, table 1 contains person info with city and table 2 contains city matched to country such as:-

Table #1

ID   Name       City
-------------------------
1    Kishan     Pokhara
2    Ram        Delhi
3    Shyam      Beijing

Table #2

City      Country
----------------------
Pokhara   Nepal
Delhi     India

I want to get the person ID, Name, Country so while joining the tables I want these items and if there is no country available for a city, I want “Unavailable” written in the country columns. Thanks

Answer

Try the below using left join and use coalesce() function to replace null country as ‘Unavailable’

select id, name, a.city,coalesce(country,'Unavailable') as country
from table1 a left join table2 b on a.city=b.city