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.

Possible duplicate of [T-SQL XOR Operator](http://stackoverflow.com/questions/5411619/t-sql-xor-operator) — shA.t, Dec 08 '16 at 08:06
@Pawel Lukasik, I had referred to this post before attempting, I agre this does explain the logic I am finding it difficult to implement as ^ (CARET) does not work with SQLServer, if i am not wrong. — Ashish, Dec 08 '16 at 09:16
@Ashish you should not use the caret (^) as this is bitwise xor operator. You should use the other answer from that post. — Paweł Łukasik, Dec 08 '16 at 09:55

score 22 · Accepted Answer · edited Aug 20 '21 at 03:10

22

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)

edited Aug 20 '21 at 03:10

Community

1
1

answered Dec 08 '16 at 07:48

Radu Gheorghiu

20,049
16
72
107

This query has a problem. It will ignore areas which match exactly 3 million km^2 or populations with exactly 2.5 million. – Tim Biegeleisen Dec 08 '16 at 07:56
@TimBiegeleisen, I merely edited the logic in the `OP's` example to get the `XOR` logic, which is why I left the area and population comparison as in the original post (`area > 30000000 | population > 25000000`). – Radu Gheorghiu Dec 08 '16 at 08:02
@Radu Gheorghiu, Thanks for the suggestion but it does not produce the required table. – Ashish Dec 08 '16 at 09:04
@Ashish Why is that? Can you post some sample data and expected results? Also, can you explain on the test data why it isn't returning the correct data? – Radu Gheorghiu Dec 08 '16 at 09:05
@RaduGheorghiu, I have tried multiple things but none are working, the link to this question is here http://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial , Q No 8,kindly select the SQLEngine to SQL Server before attempting. – Ashish Dec 08 '16 at 09:10
@Ashish Just to point out, you are comparing with the wrong numbers (and **my mistake** for copy-pasting them from your question into my answer instead of writing them myself). Your conditions are comparing the area with `30.000.000` (not 3 million) and the population with `25.000.000` (not 250 million), so you're missing and adding some zeros. Just use this query and you'll get the "happy face" on sqlzoo. – Radu Gheorghiu Dec 08 '16 at 09:21
1

Thanks @Radu, my bad, will look closer at nos in future.An important lesson indeed. – Ashish Dec 08 '16 at 09:45

score 6 · Answer 2 · answered Dec 08 '16 at 07:47

6

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.

answered Dec 08 '16 at 07:47

Tim Biegeleisen

502,043
27
286
360

I agree with your `<=` edit, although just pointing out that the question says "**more** than 3 million sq km or it has a population of **more** than 250 million", which would imply a non-inclusive comparison. – Radu Gheorghiu Dec 08 '16 at 07:55
Also, the OP's post has a non-inclusive comparison operator. (`area > 30000000 | population > 25000000`) – Radu Gheorghiu Dec 08 '16 at 08:01

David Larochette · Answer 3 · 2019-12-05T16:42:35.940

5

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.

edited Dec 05 '19 at 16:42

answered May 24 '19 at 06:49

David Larochette

1,200
10
18

SQL Server does not support comparing two boolean expressions. You cannot do "(a > b) <> (c > d)", must do "((a > b) and (c <= d)) or ((a <= b) and (c > d))" instead. – Ricardo Rocha Feb 05 '20 at 11:07
2

IIF(area > 3000000, 1, 0) <> IIF(population > 25000000) – Graham Aug 16 '21 at 21:44
Bear in mind that **IIF** function is only supported on **SQL Server 2012** and later – Julio Nobre Aug 17 '21 at 21:28

score 4 · Answer 4 · answered Jan 13 '20 at 11:23

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.

answered Jan 13 '20 at 11:23

Volkan

41
1

score 4 · Answer 5 · answered Jul 12 '20 at 20:18

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)

score 2 · Answer 6 · edited Jun 11 '21 at 16:44

2

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.

edited Jun 11 '21 at 16:44

Slava Rozhnev

9,510
6
23
39

answered Jun 11 '21 at 10:03

Sunil Singh Tomar

21
2

Why hasn't this been the acceptable answer and scored a point at least!?!? – Fandango68 Jun 06 '22 at 05:03

score 0 · Answer 7 · answered Jan 21 '20 at 14:17

0

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.

answered Jan 21 '20 at 14:17

Sanket Davane

1

score -1 · Answer 8 · edited Jul 30 '21 at 23:10

-1

The query will be like this:

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

edited Jul 30 '21 at 23:10

Salahuddin Ahmed

4,854
4
14
35

answered Jul 29 '21 at 16:41

Hari Prasad

17
1

XOR in SQL Server

8 Answers8