SQL - Replace column values with another table column ONLY where they match, else keep original value?

Question

I'm having a hard time figuring out how to do what I described in the title in an efficient way. The question is rather complicated to phrase so I'll just provide an example:

I have 2 tables - one is a lookup table for country names, the other holds counts of countries. The problem is, the 2nd table got f**k up, and now in the same column has both IDs and names of the country, like this:

Table_1: countries

 country_id | name
------------+------
    0000    | Italy
    0001    | France
    0002    | Spain
    0003    | Norway

Table_2: country_count

 country_id | count
------------+------
    Italy   |   4
    0001    |   4
    0002    |   2
    Norway  |   1

I used to use the 1st table as a lookup, and replace the IDs with actual names, using a simple INNER JOIN. However, with this situation I can't.

I worked around the issue with this query, but it's inefficient because it parses the data twice:

SELECT cs.name, cc.count
  FROM country_count cc
       INNER JOIN countries cs ON cc.country_id = cs.country_id

UNION ALL

SELECT country_id name, count
  FROM country_count
 WHERE country_id NOT IN (SELECT country_id FROM countries)
 ORDER BY count DESC

which returns the wanted result, like this:

    name    | count
------------+------
    Italy   |   4
    France  |   4
    Spain   |   2
    Norway  |   1

How can I make it simpler and more efficient?

Answer 1

Join the tables and use a CASE expression like this:

select 
  case when cc.country_id = c.name then cc.country_id else c.name end country_id,
  cc.count
from country_count cc left join countries c
on cc.country_id in (c.country_id, c.name)  

Maybe you can change to an INNER join depending on your data and requirement.
See the demo (for MySql but it is standard SQL).
Results

| country_id | count |
| ---------- | ----- |
| Italy      | 4     |
| France     | 4     |
| Spain      | 2     |
| Norway     | 1     |