CODEWITHSUNDEEP
sql-server
Ashish
Ashish

Reputation: 219

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.

Upvotes: 20

Views: 94934

Answers (9)

ABHISHEK D
ABHISHEK D

Reputation: 133

select name, population, area
from world
where area>3000000 xor population > 250000000

Upvotes: -1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

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)

Upvotes: 23

Hari Prasad
Hari Prasad

Reputation: 17

The query will be like this:

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

Upvotes: -1

Sunil Singh Tomar
Sunil Singh Tomar

Reputation: 21

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.

Upvotes: 2

Harshit Goyal
Harshit Goyal

Reputation: 73

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)

Upvotes: 5

Sanket Davane
Sanket Davane

Reputation: 1

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.

Upvotes: 0

Volkan
Volkan

Reputation: 81

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.

Upvotes: 8

David Larochette
David Larochette

Reputation: 1230

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.

Upvotes: 6

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522084

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.

Upvotes: 6

Related Questions