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?

I'm a little confused. Do you want a query that returns all five? — Les, Jul 13 '10 at 19:34
Symmetry and laziness (US american english, UK british english and BR brazilian portuguese became US english, BR portuguese and UK english). Warning: don't you go use symmetry (duplicated code) and laziness (no accessor methods) in programming! — Delirium tremens, Jul 14 '10 at 02:06

score 0 · Accepted Answer · answered Jul 13 '10 at 19:34

0

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.

answered Jul 13 '10 at 19:34

OMG Ponies

325,700
82
523
502

Mind that if you have duplicates, remove the "ALL" keyword from the `UNION ALL` portion of the query to get distinct values. – OMG Ponies Jul 13 '10 at 19:35
Wait -- there are different columns in the tables, so a) I think this query will fail and b) I don't think OP really wanted a union. The first table is country and language, but the second table is country and name. Perhaps that was a mistake in the question? Or maybe just an unclear statement of what was wanted. – MJB Jul 13 '10 at 19:39
@MJB: There are various hacks to get full outer join results in MySQL - this is one of them. Secondly, the data type matching for ordinal location in a SELECT clause is all that matters in a UNION - I'm not making judgements on the data/example provided, but you're welcome to test & confirm it returns four rows based on the example data. – OMG Ponies Jul 13 '10 at 19:47
Thanks -- my low level of expertise on non-typical MySQL was what led me to question you. Glad to learn something new, and even more glad I didn't display my ignorance as an answer. – MJB Jul 13 '10 at 19:58
@MJB: Meh, what's a few scars here & there? :) – OMG Ponies Jul 13 '10 at 20:13
1

For others ways to simulate a full outer JOIN in MySQL: http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/ – quantumSoup Jul 13 '10 at 21:27
I think DT is actually after an outer join minus an inner join. – Jul 14 '10 at 12:34

score 0 · Answer 2 · answered Jul 14 '10 at 12:32

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

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

2 Answers2