Exclude results from an SQL Query - Big by Area or Population, but not Both (Exclusive OR)

Question

I am stuck on Q.8 on this tutorial from SqlZoo which is a followup to Q.7:

Question #7

Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million.

Show the countries that are big by area or big by population. Show name, population and area.

Question #8

USA and China are big in population and big by area. Exclude these countries.

Show the countries that are big by area or big by population but not both. Show name, population and area.

The code I have had some success with is:

SELECT name, population, area
FROM world 
WHERE area > 3000000 
OR population > 250000000
AND NOT EXISTS (
    SELECT name, population, area 
    FROM world 
    WHERE name='USA' 
    OR 'China'
)

but 'China' and 'USA' still appear in my results! Can someone show me how to write a WHERE clause to remove them?

Answer 1

(Note this is OP's Question 8 - Question 7 would just be OR)

The logical operation requiring either condition A, or condition B, but not both simultaneously, is Exclusive Or, and is abbreviated to XOR

This is supported by Ansi Sql, so the answer can be obtained by using XOR in the comparison criteria, like so:

  SELECT name, population, area
  FROM world
  WHERE (population > 250000000) XOR (area > 3000000);

Re : More Intuitive Alternative

You can expand XOR to:

  WHERE ((population > 250000000) OR (area > 3000000)) 
       AND NOT ((population > 250000000) AND (area > 3000000))

But in the general sense, rather than repeating the logic determining whether a country is big (by area or population), it might be easier to understand by using a derived table, which reasons over the population and area, and then the derived columns can be reused in the filter logic in the outer query:

SELECT name, population, area
FROM
(SELECT
     name, population, area,
     CASE WHEN population > 250000000 THEN 1 ELSE 0 END AS IsBigPop,
     CASE WHEN area > 3000000 THEN 1 ELSE 0 END AS IsBigArea
   FROM world
) DerivedWorld
WHERE (IsBigPop = 1 OR IsBigArea = 1) AND NOT (IsBigPop = 1 AND IsBigArea = 1);

(and of course you could abbreviate the WHERE clause to XOR as well)