MySQL Queries: WHERE vs. HAVING – When Should I Use HAVING for Custom Columns?

Position of custom columns in MySQL queries: WHERE and HAVING

Question:

Why must the columns created in the SELECT statement be placed after the HAVING clause and not after the WHERE clause in MySQL? Also, are there any disadvantages to using WHERE 1 instead of specifying the full definition of the column?

Answer:

While other answers have touched on this topic, the key differences are as follows:

WHERE clause: Filters data before selecting, allowing any table column. It cannot use aliases or aggregate functions.

HAVING clause: Filters data after selection, allowing the use of selected columns, aliases, or aggregate functions.

Location:

Custom columns should be placed after the HAVING clause because:

• HAVING filters selected result data, including custom columns.
• WHERE filters the result data before selection, excluding custom columns.

Efficiency:

For filtering large data sets, using the WHERE clause is more efficient because it eliminates irrelevant rows before performing the selection.

Example:

Consider the following form:

<code class="language-sql">CREATE TABLE `table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `value` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8</code>

Contains 10 rows of data, with id and value values ​​ranging from 1 to 10.

Query:

<code class="language-sql">SELECT `value` v FROM `table` WHERE `value`>5; -- 获取 5 行
SELECT `value` v FROM `table` HAVING `value`>5; -- 获取 5 行</code>

Result:

Both queries return the same results, which shows that the HAVING clause can work without the GROUP BY clause.

EXPLAIN:

<code class="language-sql">EXPLAIN SELECT `value` v FROM `table` WHERE `value`>5;</code>

<code>+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | table | range | value         | value | 4       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+</code>

<code class="language-sql">EXPLAIN SELECT `value` v FROM `table` having `value`>5;</code>

<code>+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | table | index | NULL          | value | 4       | NULL |   10 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+</code>

As you can see, both WHERE and HAVING use indexes, but the number of rows returned is different. WHERE filters the data before selection, reducing the number of rows, while HAVING filters rows after selection.

Conclusion:

• Put custom columns after the HAVING clause to filter selected data.
• Use the WHERE clause to efficiently filter large data sets by excluding irrelevant rows.

The above is the detailed content of MySQL Queries: WHERE vs. HAVING – When Should I Use HAVING for Custom Columns?. For more information, please follow other related articles on the PHP Chinese website!