Which is the least expensive aggregate function in

问题:

I usally use MAX() or MIN() if a DBMS hasn't an ANY() aggregate function.

Is there something less expensive in mySQL and MS-SQL?

回答1:

MySQL does not need an ANY() aggregate.

if I do a

SELECT field1, field2, SUM(field3) FROM table1 
GROUP BY field2

Microsofts T-SQL will complain but

MySQL will just silently execute

SELECT whatever(field1), field2, SUM(.... 

Which of course is way faster than SELECT max(field1), field2, SUM(.... GROUP BY field2

MySQL supports ANY, but SELECT ANY(field1) ... doesn't work because ANY is an operator similar to IN and ALL.
see: http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

I love MySQL

回答2:

There is no ANY aggregate in ANSI SQL-92

There is the ANY qualifier to match SOME and ALL

• TSQL - SOME | ANY why are they same with different names? (SO)
• ALL, ANY, and SOME: The Three Stooges (SQL Server Pedia)

MIN and MAX are proper aggregates... completely unrelated and shouldn't be compared

Edit:

Only MySQL has this ambiguity of an "ANY" aggregate: SQL Server, Sybase, Oracle, PostGres do not. See Do all columns in a SELECT list have to appear in a GROUP BY clause

回答3:

MIN and MAX are equally (in)expensive.

回答4:

Fine, let's rephrase the original question. Instead of ANY, which is ambiguous and not SQL standard, it would be great if EXISTS-aggregate function was provided.

On several occasions I have used "count(outer.column) > 0" to imitate the "exists(outer.column)", which would be arguably cheaper since counting is discarded anyway.