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, but not br:

• 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

What you're after is called a Full Outer JOIN, which MySQL does not have explicit syntax for. Use:

SELECT cl.countrycode,
       cl.language
  FROM COUNTRYLANGUAGE cl
 WHERE NOT EXISTS(SELECT NULL
                    FROM COUNTRYNAME cn
                   WHERE cn.countrycode = cl.countrycode)
UNION ALL
SELECT cn.countrycode,
       cn.name
  FROM COUNTRYNAME cn
 WHERE NOT EXISTS(SELECT NULL
                    FROM COUNTRYLANGUAGE cl
                   WHERE cl.countrycode = cn.countrycode)

This is a good reference link, with visual examples of various JOINs.

Answer 2

Another way of approaching this would be:

select countrycode, max(language), max(name) from
(select countrycode, language, ' ' name from countrylanguage union all
select countrycode, ' ' language, name from countryname ) ilv
group by countrycode having count(*) = 1