Novice to SQL ,, need helpt to exclude some rows 3

[47redlands]

Novice to SQL need to help with coding

I have a population table and I want search the countries that are big by area or big by population.
select name, population, area from worldwhere population >250000000 or area > 3000000

The above SQL code does this, however I only wish to Show the countries that are big by area or big by population but not both.
so for example the search returns the following:

name population area

Australia 23545500 7692024
Brazil 202794000 8515767
Canada 35427524 9984670
China 1365370000 9596961
India 1246160000 3166414
Indonesia 252164800 1904569
Russia 146000000 17125242
United States 318320000 9826675


India, USA and China have both high population and big area so should not be in the results. How can I do this?
I have tried this but I think my logic is wrong?

select name, population, area from worldwhere population >250000000 or area > 3000000 and name Not IN ('USA', 'China', 'India');

 

[DaveInIowa]

Does this make sense?

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

 

[fredericofonseca]

where (population > 250000000 and area < 3000000)
or (population < 250000000 and area > 3000000)





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

try this:

select name, population, area 
from   world
where  (population > 250000000 And area <= 3000000)
       or
       (population <= 250000000 And area > 3000000)

Take a close look at the where clause. This should return the data you are looking for without having to hard code certain countries.

Your original query did not work because you are mixing "and" with "or" in your where clause, which is causing your unexpected result.

You basically have this...

select Columns 
From   Table
Where Condition1 or Condition2 and Condition3

The problem with this... the "and" condition is evaluated before the "or" condition so it looks like this:

select Columns 
From   Table
Where  Condition1 or (Condition2 and Condition3)

What you actually wanted was:

select Columns 
From   Table
Where  (Condition1 or Condition2) and Condition3

See the difference?

If you abide by the following rule, you will prevent problems. The rule is:

When mixing And's and Or's, always use parenthesis to ensure the order of evaluation.

Sometimes the parenthesis are not needed (depending on the query), but it never hurts to have them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

To address why OR does not do as you wanted: The OR used in SQL (or any other programming language) is not meant as exclusive OR, a condition with an OR is true, if at least a single condition is fulfilled. There is an extra XOR operator for exclusive or, but usually only available to XOR bits of two numeric values and not two conditions in a where clause.

Therefore such an exclusive or needs to have the extra condition as you said it, to not have both conditions fulfilled:

(condition1 or condition2) and NOT (condition1 and condition2)

This can be transformed to several equivalent expressions, eg NOT (a and b) is equal to (NOT a or NOT b) - which again also include both a and b may be false. At the end you arrive at the one or other simpler expression. Overall this is called boolean algebra and you need to formulate your natural language conditions in that mathematical model.

Bye, Olaf.

 

[Olaf Doschke]

There is a special solution in case you want one of several conditions fulfilled and no other, simply sum the fulfilled conditions this way:

Where Case when condition1 then 1 else 0 end
+Case when condition2 then 1 else 0 end
+Case when condition3 then 1 else 0 end = 1

This looks even more complicated, but is a way to compute exclusive OR for more than two conditions without going through hoops of addressing any pair of conditions.

The counterpart without cases here would test [tt](c1 OR c2 OR c3) AND NOT (c1 and c2) AND NOT (c1 and c3) AND NOT (c2 and c3)[/tt]

Bye, Olaf.