ripper234
ripper234

Reputation: 230048

Is there a performance difference between HAVING on alias, vs not using HAVING

Ok, I'm learning, bit by bit, about what HAVING means.

Now, my question is if these two queries have difference performance characteristics:

Without HAVING

SELECT x + y AS z, t.*  FROM t
WHERE 
x = 1 and
x+y = 2

With HAVING

SELECT x + y AS z, t.*  FROM t
WHERE 
x = 1
HAVING
z = 2

Upvotes: 1

Views: 1925

Answers (2)

fredt
fredt

Reputation: 24352

HAVING is used for queries that contain GROUP BY or return a single row containg the result of aggregate functions. For example SELECT SUM(scores) FROM t HAVING SUM(scores) > 100 returns either one row, or no row at all.

The second query is considered invalid by the SQL Standard and is not accepted by some database systems.

Upvotes: 1

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

Yes it should be different - (1) is expected to be faster.

Having will ensure that first the main query is run and then the having filter is applied - so it basically works on a the dataset returned by the (query minus having).

The first query should be preferable, since it does not select those records at all.

Upvotes: 1

Related Questions