2

In Sql What gets evaluated first 'where' or 'having'

Adam Goldberg
  • 223
  • 5
  • 16
  • A general question about databases (or a question with no database specified) is unlikely to be a duplicate of a database-specific question and its answers. – Gordon Linoff Dec 20 '16 at 16:23

3 Answers3

5

There are exceptions, but this is generally the order:

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • 1
    . . There are actually numerous exceptions to this rule, based on the fact that SQL is a descriptive language not a procedural language. This ordering is used to explain the scoping rules for aliases, which takes place during the compilation phase of the query, not the execution phase. For instance, this does not explain why aliases defined in the `SELECT` are allowed in `GROUP BY` and `HAVING` clauses for Postgres and MySQL. – Gordon Linoff Dec 20 '16 at 16:32
  • I don't know why I said I wasn't sure if there were exceptions, as I know I've encountered issues that demonstrated them before, but couldn't quickly recall any examples. I believe the most common issue I've seen is a conversion in the `SELECT` clause fail even though all records that would cause it to fail would be filtered out by the `WHERE` criteria. – Hart CO Dec 20 '16 at 16:54
2

The where gets evaluated first, because it is evaluated before the group by.

Having is only evaluated after the aggregation takes place.

In general, questions like this don't make much sense because SQL is not a procedural language -- a SQL query describes a result set but not the steps that go into producing it. In this case, the answer is pretty clear (similarly, "join"s happen before "group by"s in general).

A SQL optimizer could find a way to evaluate some having conditions before aggregation. However, I'm not familiar with such optimizations.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

The answer is very similar to the one of HartCo, but it consider also additionally DISTINCT and LIMIT (or equivalent)

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. DISTINCT clause
  7. ORDER BY clause
  8. Limit number of rows clause (for example LIMIT in MySql, TOP in SQL Server)

You can find a complete explanation here

Davide Lorenzo MARINO
  • 26,420
  • 4
  • 39
  • 56