CODEWITHSUNDEEP
mysqlsqldatabaseperformancesyntax
Real Dreams
Real Dreams

Reputation: 18020

Are "having" and "where" totally equivalent when there is no "group by" clause?

Generally, is there any difference between having and where clauses when there is no group by clause (particularly in MySQL, PostrgrSQL, Oracle and MSSQL)?

select * from user where foo > 3 seems to be equivalent to select * from user having foo > 3

Upvotes: 1

Views: 320

Answers (3)

Rick James
Rick James

Reputation: 142366

Another diff... Aliases are recognized by HAVING but not WHERE:

mysql> SELECT city AS c FROM Canada WHERE c='Toronto';
ERROR 1054 (42S22): Unknown column 'c' in 'where clause'
mysql> SELECT city AS c FROM Canada HAVING c='Toronto';
+---------+
| c       |
+---------+
| Toronto |
+---------+

(Further checks showed that HAVING is not using an index when WHERE is. Version 5.6.12.)

Upvotes: 2

axiac
axiac

Reputation: 72256

This is what the documentation says on the SELECT statement page:

  • The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

    The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

  • Do not use HAVING for items that should be in the WHERE clause. For example, do not write the following:

    SELECT col_name FROM tbl_name HAVING col_name > 0

    Write this instead:

    SELECT col_name FROM tbl_name WHERE col_name > 0

  • The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

    SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10

Also, on the optimization of WHERE clauses page:

Some of the optimizations performed by MySQL follow:

  • HAVING is merged with WHERE if you do not use GROUP BY or aggregate functions (COUNT(), MIN(), and so on).1

  • Before each row is output, those that do not match the HAVING clause are skipped.

1 Some tests revealed that the merging of HAVING into WHERE (when the above conditions apply) does not make these clauses equivalent. Using the EXPLAIN statement revealed that the indexes on columns from the HAVING clause are not used.

Also read @tudor-constantin's answer and the discussion it generated.

How it works

The HAVING clause is processed at the end, just before the result set is sent back to the client while the WHERE clause is processed early in the query execution.

It makes a big difference when the query JOINs two or more tables. A non-matching WHERE condition that uses columns from the second table stops the retrieval of data from the second and the next tables but when it stays in the HAVING clause it lets the data retrieval continue and drops the row after a lot of hard work was done to get it.

A difference also exists for queries that use a single table: the indexes on columns from the HAVING clauses are not used to filter the rows read from the table. Indexes are used to filter what rows are read from the table (sometimes an index can even suppress the read of the table data entirely) and for sorting. Not using indexes leads to slow queries.

The final resolution

The WHERE and HAVING clauses are not equivalent. Even if they produce the same final result, there is a huge difference in the way the queries are executed.

Putting in the HAVING clause conditions that can stay in the WHERE clause (see above) produces a waste of resources (CPU processing power, data-storage access, time) for nothing.

Upvotes: 3

Tudor Constantin
Tudor Constantin

Reputation: 26861

There's at least one huge difference:

The HAVING clause will not use an index:

explain select username from users having user_id = 32654456;
+----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | index | NULL          | PRIMARY | 32      | NULL | 661107 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+

While the WHERE clause will:

explain select username from users where user_id = 32654456;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 32      | const |    1 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+

Upvotes: 3

Related Questions