CODEWITHSUNDEEP
mysqlsql
Darshan K N
Darshan K N

Reputation: 111

Performance of " > ALL" VS "MAX()" in SQL

Sample Table invoices

invoice_total client_id
2000 1
3000 1
2500 3
50 3

Code-1

    SELECT * 
    FROM invoices
    WHERE invoice_total > (
     SELECT MAX (invoice_total)
     FROM invoices
     WHERE client_ID =3
    )

vs

Code-2

    SELECT * 
    FROM invoices
    WHERE invoice_total >  ALL (
     SELECT invoice_total
     FROM invoices
     WHERE client_ID =3
    )

I was going through some online SQL videos where the instructor said both are equivalent. Although both produce the same result, performance-wise isn't the code-1 better than code-2?

As far as I understand: In the case of code-1 the DB is processed twice: once to get max() and second to get all values greater than the result of max(). Complexity = N+K

while in the case of code-2 each row is compared to the list of results from the subquery. Complexity = N*K.

Or does internal optimization take place and both have the same performance?

Upvotes: 3

Views: 559

Answers (2)

MOUSA Rezvankhah
MOUSA Rezvankhah

Reputation: 66

Query 1

 Scan count 2, logical reads 2
---------------
 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 13 ms.
---------------
Query Cost is 49%

sql optimizer use Inner Join for get result between subquery and outer query

Query 2

 Scan count 2, logical reads 5
---------------
 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 159 ms.
---------------
Query Cost is 51%

sql optimizer use Left Join for get result between subquery and outer query

subquery cost in both of Queries are equal. I think Query1 is better than Query2

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270593

They do not produce the same result.

If there are no invoices for client = 3, then the ALL returns all rows. The MAX() returns no rows.

As for performance, you should test on your database. MySQL probably optimizes them similarly, but there could be differences because the meaning is different.

Upvotes: 5

Related Questions