XOR in SQL Server

I have to display the countries that are big by area or big by population but not both and Show name, population and area. Basically it's a XOR operation if i am not wrong.

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.

I have tried this

SELECT name, population, area FROM world WHERE (area > 30000000 | population > 25000000) & (area < 30000000 & population < 25000000) 

I am trying this on sqlzoo.net - SELECT_from_WORLD_Tutorial: Q.No-8. Please select the SQL Engine to SQLSERVER.

8 Answers

You can implement a XOR like this - don't forget that the question will require you to use <= to correctly use the XOR operator:

SELECT name , population , area FROM world WHERE (area > 3000000 AND population <= 250000000) OR (area <= 3000000 AND population > 250000000) 

SELECT name, population, area FROM world WHERE (area > 3000000 AND population <= 25000000) OR -- big area, small population (area <= 3000000 AND population > 25000000) -- small area, big population 

Note that I used <= to represent the "smaller then" condition. This is to avoid a situation where an area equals 3 million km^2 or a population exactly equals 2.5 million. Using < would eliminate data in this case.

SELECT name, population, area FROM world WHERE (area > 3000000) <> /* XOR */ (population > 25000000) 

Is briefer albeit less readable. As a general rule, <> or != is a good replacement for logicial XOR.

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

The question wants you to answer like this. This is the correct and short way of using 'XOR' for this question.

SELECT name, population, area FROM world WHERE (area > 3000000 OR population > 250000000) AND NOT (area > 3000000 AND population > 250000000) 

Imagine this through a Venn diagram where a = area > 3000000, b = population > 250000000

a XOR b would be equal to (a Union b) Minus (a Intersection b)

SELECT NAME, population, area FROM world WHERE area > 3000000 OR population > 250000000 EXCEPT SELECT NAME, population, area FROM world WHERE area > 3000000 AND population > 250000000 

Above is the actual representation in set theory.

Select name, population, area from world where (population >= 250000000 AND NOT area >= 3000000) OR (area >= 3000000 AND NOT population >= 250000000); 

This will sure give you your desire answer.

The query will be like this:

SELECT name, population, area FROM world WHERE (area > 3000000 AND population < 250000000) OR (area < 3000000 AND population > 250000000);