Xor in Sql Server

Question

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.

Accepted Answer

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)

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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)

Answer 6

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.

Answer 7

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.

Answer 8

The query will be like this:

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

Xor in Sql Server

Must Read

8 Answers

Chloe Bennett