What are the differences between the WHERE clause and the HAVING clause?

In addition to being able to group data using GROUP BY, MySQL also allows you to filter which groups to include and which groups to exclude. For example, you might want to list all customers who have made at least two orders. To obtain this data you must filter based on the complete group, not on individual rows.

WHERE filters rows, not group. WHERE has no idea what a group is. HAVING is similar to WHERE. In fact, HAVING can be used in place of WHERE. The only difference: WHERE filter rows, HAVING filter groups.

SELECT id, COUNT(*) AS orders FROM order GROUP BY id HAVING COUNT(*) >= 2;

The WHERE clause filters the data before data is grouped, and the HAVING clause filters the result after data is grouped. Rows that were eliminated by WHERE clause are not included in the group.

SELECT order, SUM(quantity * price) AS order_total FROM orderitems GROUP BY order HAVING SUM(quantity * price) >= 50 ORDER BY order_total;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License