CODEWITHSUNDEEP
sqloracle-database
cominghomesomeday
cominghomesomeday

Reputation: 43

sql Difference between >=All and MAX

Whats the difference between

Statement 1:

SELECT continent, name, area FROM world x
  WHERE x.area >= ALL
    (SELECT y.area FROM world y
    WHERE y.continent=x.continent)

Statement 2:

SELECT continent,name,area FROM world x
WHERE x.area =(SELECT MAX(y.area) FROM world y
WHERE y.continent=x.continent)

It seems to give me different results AS shown below.

For statement 1: http://s8.postimg.org/6t8588cz9/ss1.jpg

For statement 2: http://s4.postimg.org/5lfo10xul/ss2.jpg

Isn't >= ALL logically equivalent to SELECT MAX()?

The second statement which uses the MAX returns a superset of the first statement.(More results as compared to the first statement) I cant figure out why it is so.

Upvotes: 2

Views: 2191

Answers (2)

Vrashabh Irde
Vrashabh Irde

Reputation: 14367

The problem with this particular data set is that some of the continents like 'Africa' and 'Europe' have null data for area (E.g. Netherlands in Europe)

Add area>0 to your first query and you should see the same results 

SELECT continent, name, area FROM world x
  WHERE x.area >= ALL
    (SELECT y.area FROM world y
    WHERE y.continent=x.continent and area>0)

Upvotes: 4

Bohemian
Bohemian

Reputation: 425238

An important difference is that 

x = (select max(y) ...)

will only be true when x is exactly equal to the maximum y, so x must be found in y, but

x >= all (select y ...)

will be true for any values of x greater than (or equal to) all values of y, so x may be not found in y.

Upvotes: 2

Related Questions