Value> = all (select v2 ...) produces different results from the value = (select max (v2) ...)

Question

Here I asked a question about a non-working request.

By chance (with a single answer) I found how to solve the problem correctly. The problem is that I do not understand why they give different results.

So, the database has the following schema:

And I am looking for all the models from PC , Printer and Laptop with the highest price. All these tables can have a unique model column, since elements with different code can have the same model.

My initial decision was:

 with model_price(model,price) as ( select model,price from PC union select model,price from Laptop union select model,price from Printer ) select model from model_price where price >= all(select price from model_price) 

He gave the wrong result - the system returned * Wrong number of records (less by 2) .

The corrected solution that works is this:

 with model_price(model,price) as ( select model,price from PC union select model,price from Laptop union select model,price from Printer ) select model from model_price where price = (select max(price) from model_price) 

So why does a solution with all give an excellent result?

---

_{About the sql engine: Now we use Microsoft SQL Server 2012 on the rating stages, and MySQL 5.5.11, PostgreSQL 9.0, and Oracle Database 11g on the learn stage in addition. So I don’t know which engine he uses to illuminate this exercise.}

Answer 1

 create table t (f int null); select 1 where 1 >= (select max(f) from t); -- 1 select 1 where 1 >= all(select f from t); -- 2 insert into t values (null), (0); select 1 where 1 >= (select max(f) from t); -- 3 select 1 where 1 >= all(select f from t); -- 4 

http://www.sqlfiddle.com/#!6/3d1b1/1

The first select returns nothing, the second select returns 1 .

MAX returns a scalar value. If there are no rows, MAX returns NULL . 1 >= NULL is not true on line 1. On the other hand, 1 >= all f true because there is generally no f for which the condition is not true.

The third select returns 1 , the fourth select returns nothing.

MAX , like all aggregated functions, ignores NULL s. MAX(f) is 0 in line 3, and 1 >= 0 is true. ALL not: it evaluates 1 >= NULL AND 1 >= 0 on line 4, which is incorrect.