CODEWITHSUNDEEP
sqlmysqldatabasehaving
ripper234
ripper234

Reputation: 230028

What is the semantic difference between WHERE and HAVING?

Let's put GROUP BY aside for a second. In normal queries (without GROUP BY), what is the semantic difference? Why does this answer work? (put an alias in a HAVING clause instead of WHERE)

Upvotes: 1

Views: 512

Answers (3)

grahamparks
grahamparks

Reputation: 16296

Very broadly, WHERE filters the data going into the query (the DB tables), while HAVING filters the output of the query.

Statements in the WHERE clause can only refer to the tables (and other external data sources), while statements in the HAVING clause can only refer to the data produced by the query.

Upvotes: 1

Nick Bastin
Nick Bastin

Reputation: 31319

HAVING operates on the summarized row - WHERE is operating on the entire table before the GROUP BY is applied. (You can't put GROUP BY aside, HAVING is a clause reserved for use with GROUP BY - leaving out the GROUP BY doesn't change the implicit action that is occurring behind the scenes).

It's also important to note that because of this, WHERE can use an index while HAVING cannot. (In super trivial un-grouped result sets you could theoretically use an index for HAVING, but I've never seen a query optimizer actually implemented in this way).

Upvotes: 3

BoltClock
BoltClock

Reputation: 723598

MySQL evaluates the query up to and including the WHERE clause, then filters it with the HAVING clause. That's why HAVING can recognize column aliases, whereas WHERE can't.

By omitting the GROUP BY clause, I believe you simply tell the query not to group any of your results.

Upvotes: 2

Related Questions