get us, uk in one table, cn and jp in the other, but not br in both

Question

in countrylanguage,

countrycode | language  
US          | English  
BR          | Portuguese  
UK          | English  

in countryname,

countrycode | name  
         CN | China  
         BR | Brazil  
         JP | Japan  

"an inner join produces results by selecting combinations of matching rows from the joined tables. However, it cannot find non-matches"

"A left join treats the left table (the first one named) as a reference table and produces output for each row selected from it, whether or not the row is matched by rows in the right table"

To get us, uk, cn and jp:

• inner joins can't find mismatches (br <> br wouldn't work).
• outer joins would find all in one (us and uk) or all in the other (cn and jp).

Do you use two outer joins?

Answer 1

Something like this. Use only the inner query if you want them in separate columns.

select
  coalesce(c1, c2) countrycode
from
  (
    select
      t1.countrycode c1,
      t2.countrycode c2
    from
      countrylanguage t1
      full outer join countryname t2 on t1.countrycode = t2.countrycode
    where
      t1.countrycode is null or t2.countrycode is null
  ) x

This wikipedia article is a good place to start learning about joins, then move on to your RDBMS-specific documentation.